I am attempting to create a measure that calculates year-over-year growth with detailed categorization on the nature of the growth/decline. Here are the four categories that I need to capture at the store/unit level:

New Unit - Units in their first twelve months of existence (i.e., operational in current period but not in prior year period)
New Install - Units @ Month 13 (i.e., one-year anniversary of first operational period); these are separated b/c the first month generates install revenues which are higher than other months
Same-Store Sales - Units operational in both current period and prior year period
Churn - Units operational in prior year period but not operational in current year period
I have made several attempts at this and have figured out an interim solution that is very cumbersome. The issue within the PBI environment appears to be periods where there are no sales in current period but there ARE sales in prior period. I don’t know how to assign those a category. My attempts in PBI have included creating calculated columns which look at the current period relative to the prior period both relative to the first and last operational month. Again, for units that have churned, I am not able to pick up any of the churn months (i.e., twelve months after churn). Same story for units with zero sales in current month and positive sales in prior year month.

The way that I have solved for this is manipulating the Sales Data in Power Query. Essentially pivoting the data to show all months in the headers and then replacing all null values with zero and then unpivoting. From there, I am able to calculate all non-zero YoY changes and use logic to assign to a bucket. As you might imagine this creates a ton of data (esp w/ 25k units) and takes a long time to load

Question

I am attempting to create a measure that calculates year-over-year growth with detailed categorization on the nature of the growth/decline. Here are the four categories that I need to capture at the store/unit level:

New Unit - Units in their first twelve months of existence (i.e., operational in current period but not in prior year period)
New Install - Units @ Month 13 (i.e., one-year anniversary of first operational period); these are separated b/c the first month generates install revenues which are higher than other months
Same-Store Sales - Units operational in both current period and prior year period
Churn - Units operational in prior year period but not operational in current year period
I have made several attempts at this and have figured out an interim solution that is very cumbersome. The issue within the PBI environment appears to be periods where there are no sales in current period but there ARE sales in prior period. I don’t know how to assign those a category. My attempts in PBI have included creating calculated columns which look at the current period relative to the prior period both relative to the first and last operational month. Again, for units that have churned, I am not able to pick up any of the churn months (i.e., twelve months after churn). Same story for units with zero sales in current month and positive sales in prior year month.

The way that I have solved for this is manipulating the Sales Data in Power Query. Essentially pivoting the data to show all months in the headers and then replacing all null values with zero and then unpivoting. From there, I am able to calculate all non-zero YoY changes and use logic to assign to a bucket. As you might imagine this creates a ton of data (esp w/ 25k units) and takes a long time to load

Accepted Answer

Based on your explanation, a possible DAX solution could involve measures and variable. The logic can be implemented within a set of DAX measures.

For the sake of this example, let's assume you have a fact table named SalesTable with the columns StoreID, SaleDate, and Revenues. We'll also use the Dates table for our date-time intelligence functions. Here's a partially-fleshed out framework for your solution in DAX.

```dax
// Calculate New Unit
New Unit =
VAR CurrentYear = YEAR(MAX(Dates[Date]))
VAR PriorYear = CurrentYear - 1
RETURN 
  CALCULATE(
    COUNT(SalesTable[StoreID]),
    FILTER(
        SalesTable,
        YEAR(SalesTable[SaleDate]) = CurrentYear && ISBLANK(CALCULATE(COUNT(SalesTable[StoreID]), YEAR(SalesTable[SaleDate]) = PriorYear))
    )
  )

// Calculate New Install
New Install =
VAR CurrentYear = YEAR(MAX(Dates[Date]))
VAR PriorYear = CurrentYear - 1
RETURN 
  CALCULATE(
    COUNT(SalesTable[StoreID]),
    FILTER(
        SalesTable,
        YEAR(SalesTable[SaleDate]) = CurrentYear && YEAR(MAX(SalesTable[SaleDate])) = PriorYear
    )
  ) - [New Unit]

// Calculate Same-Store Sales changes
Same-Store Sales =
VAR CurrentYear = YEAR(MAX(Dates[Date]))
VAR PriorYear = CurrentYear - 1
RETURN 
  CALCULATE(
    SUM(SalesTable[Revenues]),
    FILTETER(
        SalesTable,
        YEAR(SalesTable[SaleDate]) = CurrentYear && YEAR(MAX(SalesTable[SaleDate])) = PriorYear
    )
  )

// Calculate Churn
Churn =
VAR CurrentYear = YEAR(MAX(Dates[Date]))
VAR PriorYear = CurrentYear - 1
RETURN 
  CALCULATE(
    COUNT(SalesTable[StoreID]),
    FILTER(
        SalesTable,
        YEAR(SalesTable[SaleDate]) = PriorYear && ISBLANK(CALCULATE(COUNT(SalesTable[StoreID]), YEAR(SalesTable[SaleDate]) = CurrentYear))
    )
  )

```

Remember, good practices are key when working with DAX and understanding the relationships between your tables. For more in-depth learning take additional courses at Enterprise DNA's platform to master all these techniques.

Please note that these codes are an indicative framework, you might need to adjust depending on your actual data strcuture and more complex queries.

Code Generator | DAX

DAX Solution Framework for Sales Analysis

Prompt

Answer

Description

More Code Generators

Creators

Debuggers

Visualizers

Advisors

tools

languages

skills

plans

Links