Forum Discussion
Dynamic 12 Month YTD Calculation
Hi asa70 ,
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have modified few things in pbix and successfully implemented it.
I am also including .pbix file for your better understanding, please have a look into it:
Thank you for using Microsoft Community Forum.
Hi jaineshp and danextian , thank you for both your suggestions. I have tried them, however, I didn't get the required result. I have shared an example of the required result below:
If you look at the required result, the Max date here is June 2025, which means the start date is July 2024 as highlighted. So technically, not a rolling total either because there is an end point and it will constantly shift as the months go by.
Regards,
Asa
Hey asa70,
Thanks for the clarification and the example image. I can see exactly what you need now - a dynamic 12-month period that's always anchored to the maximum date in your dataset.
The issue with your formulas 2-4 is that they're creating rolling periods that shift with the row context, rather than a fixed 12-month window.
Try this formula:
Attrition 12M =
VAR MaxDateInData = MAX(ALL(_CALENDAR[Date]))
VAR StartDate = EDATE(MaxDateInData, -11)
RETURN
CALCULATE(
SUM(Attrition[value]),
FILTER(
ALL(_CALENDAR[Date]),
_CALENDAR[Date] >= StartDate &&
_CALENDAR[Date] <= MaxDateInData
)
)
Key points:
- MAX(ALL(_CALENDAR[Date])) gets the absolute maximum date in your entire dataset
- EDATE(MaxDateInData, -11) goes back 11 months to create a 12-month window
- This creates a fixed date range that doesn't shift with row context
Expected behavior:
- With June 2025 as max date: July 2024 to June 2025 (as shown in your example)
- When July 2025 data is added: August 2024 to July 2025
- All rows will show the same consistent 12-month total
This should give you exactly the behavior shown in your required result. Let me know if this works for you!
Did it work? ✔ Give a Kudo • Mark as Solution – help others too!