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!Hey asa70,
Really appreciate your feedback!I can see the issue with the previous suggestions. You need a fixed 12-month window that's always anchored to the maximum date in your dataset, not a rolling calculation.
Here's the solution that should work:
Attrition 12M YTD =
VAR MaxDateInData = CALCULATE(MAX(_CALENDAR[Date]), ALL(_CALENDAR))
VAR StartDate = EDATE(MaxDateInData, -11)
RETURN
CALCULATE(
SUM(Attrition[value]),
ALL(_CALENDAR),
_CALENDAR[Date] >= StartDate && _CALENDAR[Date] <= MaxDateInData
)Key differences from previous attempts:
- CALCULATE(MAX(_CALENDAR[Date]), ALL(_CALENDAR)) - This ensures we get the absolute maximum date across the entire dataset, removing any filter context
- ALL(_CALENDAR) in the main CALCULATE - This removes all existing filters on the calendar table
- Fixed date range logic - The filter creates a consistent window from StartDate to MaxDateInData
Expected behavior:
- With June 2025 as max date in your dataset: Shows sum from July 2024 to June 2025 (51 total)
- When July 2025 data is added: Will show sum from August 2024 to July 2025
- Every row will show the same value (51 in your example) because it's always looking at the same fixed 12-month period
This creates a true "12-month to date" measure that shifts only when new months are added to your dataset, not a rolling total that changes with each row.
Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
Best regards,
Jainesh Poojara / Power BI DeveloperHey asa70,
Appreciate your feedback.
Try this updated one: -
Attrition 12M YTD =
VAR CurrentDate = MAX(_CALENDAR[Date])
VAR StartDate = EDATE(CurrentDate, -11)
RETURN
CALCULATE(
SUM(Attrition[value]),
ALL(_CALENDAR),
_CALENDAR[Date] >= StartDate && _CALENDAR[Date] <= CurrentDate
)
Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
Best Regards,
Jainesh Poojara | Power BI Developer
