Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I would like to calculate the delta of a value between dates that occur at non-regular intervals, grouped by category. A simplified example of the data is below.
I know there are quick measures for things like year-over-year, or year-over-month change, but I was struggling to adapt these. I did manage to create a calculated column with the previous date in each row grouping by category, and if I can extend this to determine the Value for that previous date, I can take the difference that way... I am open to other approaches as well.
Thank you for your time - Alex
Previous Date = CALCULATE(MAX(TimeSeriesData[Date]), FILTER(TimeSeriesData, EARLIER(TimeSeriesData[Date])>TimeSeriesData[Date] && EARLIER([Category])=TimeSeriesData[Category]))
@Anonymous , I am open to other approaches and would appreciate if you would explain your proposal. Thanks.
OK. This should be done in PQ for sure. If the table is reasonably small, you could do this:
[Previous Date] = -- calculated column var __currentDate = TSD[Date] var __currentCat = TSD[Category] var __prevDate = MAXX( FILTER( TSD, TSD[Date] < __currentDate && TSD[Category] = __currentCat ), TSD[Date] ) return __prevDate [Previous Value] = -- calculated column var __currentCat = TSD[Category] var __prevDate = TSD[Previous Date] -- calculated column above var __prevValue = MAXX( FILTER( TSD, TSD[Date] = __prevDate && TSD[Category] = __currentCat ), TSD[Value] ) return __prevValue
Now you're able to create another calculated column that will hold the difference in values. But you could also create only one calculated column telescoping the formulas into one...
Best
Darek
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |