Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I would like to find or refer to the previous value in a time series so I can do a simple difference calculation between the current and the previous value.
The database i have has a date column, frequency, value. Each time series has different reporting frequency such as monthly, quarterly, yearly. I would like to be able to add another column in the database to show the prior value for the specific time series. Is this possible when the database has varying reporting frequencies?
Any help would be much appreciated
Give a try to below.
Add a new column LastFreqValue using below dax.
LastFreqValue = SWITCH ( 'Sample'[Frequency], "Monthly", CALCULATE ( SUM ( 'Sample'[Value] ), FILTER ( 'Sample', 'Sample'[Frequency] = "Monthly" ), PREVIOUSMONTH ( 'Sample'[Date] ) ), "Quarterly", CALCULATE ( SUM ( 'Sample'[Value] ), FILTER ( 'Sample', 'Sample'[Frequency] = "Quarterly" ), PREVIOUSQUARTER ( 'Sample'[Date] ) ), "Annually", CALCULATE ( SUM ( 'Sample'[Value] ), FILTER ( 'Sample', 'Sample'[Frequency] = "Annually" ), PREVIOUSYEAR ( 'Sample'[Date] ) ) )
Now add another column to calculate difference from Value to LastFreqValue using below formula
Diff = IF ( ABS ( 'Sample'[LastFreqValue] ) > 0, 'Sample'[Value] - 'Sample'[LastFreqValue], 0 )
I have tested on my sample data and here is the result. I have used only one index DAX in my sample. you might need to handle filtering based on this.
Hi Habib,
Thanks for your suggestion. I have followed your instructions and it has not worked on my end. If i need to apply filtering to complete the job, how or where would you suggest putting it?
Kind regards,
Seb
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |