Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi everybody!
I have a problem which should be simple, however I don't know why I cannot get the correct result.
I have a table called 'Reporting' with two columns: [Report date] and [Accumulated value]. The [Accumulated value] column is in fact a rolling sum which resets every January.
The table with the desired output (last two columns) is
Report Date | Accumulated value | Monthly value (current month - previous month) | Last 12 months sum of Monthly values (including current month) |
1-Jan-2019 | 100 | 100 | |
1-Feb-2019 | 150 | 50 | |
1-Mar-2019 | 180 | 30 | |
1-Apr-2019 | 220 | 40 | |
1-May-2019 | 270 | 50 | |
1-Jun-2019 | 300 | 30 | |
1-Jul-2019 | 310 | 10 | |
1-Aug-2019 | 330 | 20 | |
1-Sep-2019 | 370 | 40 | |
1-Oct-2019 | 400 | 30 | |
1-Nov-2019 | 460 | 60 | |
1-Dec-2019 | 500 | 40 | |
1-Jan-2020 | 50 | 50 | 450 |
1-Feb-2020 | 110 | 60 | 460 |
1-Mar-2020 | 150 | 40 | 470 |
1-Apr-2020 | 170 | 20 | 450 |
1-May-2020 | 200 | 30 | 430 |
1-Jun-2020 | 220 | 20 | 420 |
1-Jul-2020 | 270 | 50 | 460 |
1-Aug-2020 | 290 | 20 | 460 |
1-Sep-2020 | 370 | 80 | 500 |
1-Oct-2020 | 400 | 30 | 500 |
1-Nov-2020 | 440 | 40 | 480 |
1-Dec-2020 | 490 | 50 | 490 |
For the [Monthly Value] measure I get the correct results using the formula
Monthly Value = if(MONTH(MAX(Reporting[Report Date]))=1, SUM(Reporting[Accumulated value]), SUM(Reporting[Accumulated value])-CALCULATE(sum(Reporting[Accumulated value]),PREVIOUSMONTH('Calendar'[Date]))) |
For the [Last 12 months sum] measure I'm trying with the formula
Last 12 months sum = CALCULATE([Monthly Value] , DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]),-12,MONTH)) |
however instead of the needed value, I'm getting the rolling sum of the [Accumulated value] column (100, 250, 430 etc.).
What am I doing wrong?
Any help would be highly appreciated.
Thank you!
Solved! Go to Solution.
@Laurix , Create measure like
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Thank you, @amitchandak !
My formula for the Current Value was also returning the correct values, however the SUMX for the rolling sum for the last 12 months did the trick (I knew I needed a SUMX there, it was that simple...).
All the best!
@Laurix , Create measure like
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |