Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I would like to calculate correctly a total of a sales measure to show a rolling cumulative total for the last 12 months.
This measure is intented to work with 2 calendars - first main calendar is used to select data in the slicer and second calendar is used to display data on the visual only until the selected month from the first calendar and slicer.
The goal is to display the data only until the time period selected in the year/month slicers meaning that if I select April 2024 I should see the total result calculating May 2023 up to April 2024. For a single month I get correct values, however the totals at the moment return only data relevant to 2024.
This is the measure:
Do you know what needs to be added to my measure in order to solve this?
Any other ideas how to fix this?
Thank you.
Solved! Go to Solution.
It's a rolling 12 Month Measure.
Sales - Last 12 Months =
CALCULATE (
[Sales],
DATESINPERIOD ( Calendar[Date], MAX ( Calendar[Date] ), -12, MONTH )
)
Sales is a simple Sum
SUM(Sales[Amount])
Proud to be a Super User! | |
Date tables help! Learn more
Hi @BI_Samurai
Create an Inactive relationship between Calendar 2 and Calendar on the Date columns. Create a measure similar to below. Add the Date columns in the visual from Calendar2 and the Slicer data should come from Calendar.
Sales P12M =
VAR RefDate =
MAX ( Calendar[Date] )
VAR PrevDate =
DATESINPERIOD ( Calendar2[Date], RefDate, -12, MONTH )
RETURN
CALCULATE (
[Sales - Last 12 Months],
REMOVEFILTERS ( Calendar ),
KEEPFILTERS ( PrevDate ),
USERELATIONSHIP ( Calendar[Date], Calendar2[Date] )
)
Hope this helps
Joe
If you found my answer helpful and it solved your issue, please accept as solution
Proud to be a Super User! | |
Date tables help! Learn more
Awesome, thank you !
Hello,
thanks for a quick reply and the tip. What is the exact measure that you use here [Sales - Last 12 Months] ?
thank you
It's a rolling 12 Month Measure.
Sales - Last 12 Months =
CALCULATE (
[Sales],
DATESINPERIOD ( Calendar[Date], MAX ( Calendar[Date] ), -12, MONTH )
)
Sales is a simple Sum
SUM(Sales[Amount])
Proud to be a Super User! | |
Date tables help! Learn more
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
30 | |
16 | |
14 | |
14 | |
9 |