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.
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