powerbidaxdata-analysispowerbi-desktopcalculated-columns

DAX query: calculating incremental growth of a value by date


Hi Hoping someone can help me out with this Dax query to create a new column of calculated values in an existing table. I'm trying to add a new column that for each row shows the incremental growth in usage over time for each unique combination of CustomerName and ProductName.

CustomerName ProductName Usage Date
CustomerA service A 2570 2023-06-01
CustomerA service A 2539 2023-07-01
CustomerA service A 2579 2023-08-01
CustomerA service A 2581 2023-09-01
CustomerB Service B 153 2023-06-01
CustomerB Service B 406 2023-07-01
CustomerB Service B 603 2023-08-01
CustomerB Service C 15 2023-06-01
CustomerB Service C 16 2023-07-01
CustomerB Service C 13 2023-08-01

I've tried the following Dax query, but it's not properly calculating the growth.

Calc_IncGrowthOverTime = 
VAR CurrentCustName = [CustomerName]
VAR CurrentWorkLoad = [WorkloadName]
VAR EarliestDate = CALCULATE(MIN('Table1'[Date]), ALL('Table1'), 'Table1'[CustomerName] = CurrentCustName, 'Table1'[WorkloadName] = CurrentWorkLoad)
VAR SoYValue = CALCULATE(MIN('Table1'[Usage]), 'Table1'[Date] = EarliestDate, 'Table1'[CustomerName] = CurrentCustName, 'Table1'[WorkloadName] = CurrentWorkLoad)
RETURN
[Usage] - SoYValue

For rows that aren't the earliest date for that combination of CustomerName and ProductName it's caculating SoYValue as zero so isn't properly calculating the growth.

What I'd like it to return is this... expected return


Solution

  • Growth = 
    VAR x = CALCULATE(MIN('Table'[Date]), ALLEXCEPT('Table', 'Table'[CustomerName], 'Table'[ProductName]))
    VAR y = CALCULATE(MIN('Table'[Usage]), ALLEXCEPT('Table', 'Table'[CustomerName], 'Table'[ProductName]), 'Table'[Date] = x)
    RETURN 'Table'[Usage] - y
    

    enter image description here