Forum Discussion

asa70's avatar
asa70
Advocate I
1 year ago
Solved

Dynamic 12 Month YTD Calculation

Hi all,    I hope you are well,   I'm currently doing a 12 month YTD calculation and what should be a simple calculation, is proving to be quite the challenge. I think I'm missing something in be...
Need Help
  • v-sshirivolu's avatar
    v-sshirivolu
    11 months ago

    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.

asa70's avatar
asa70
Advocate I
1 year ago

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

jaineshp's avatar
jaineshp
Memorable Member
1 year ago

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:

  1. CALCULATE(MAX(_CALENDAR[Date]), ALL(_CALENDAR)) - This ensures we get the absolute maximum date across the entire dataset, removing any filter context
  2. ALL(_CALENDAR) in the main CALCULATE - This removes all existing filters on the calendar table
  3. 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 Developer