Forum Discussion
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 between. The aim is to have a dynamic "YTD" calculation based on the last 12 months. Example: If I'm reporting in July 2025, then the months would be August 2024 to July 2025. If I use:
- Attrition YTD = Calculate( Sum(Attrition[value]),
Datesytd(_calendar[date],"06-30")) -
This is the one comes the closest, the only issue is, there is no fiscal year and it changes as each new month gets added to the dataset. - Attrition YTD = Calculate( Sum(Attrition[value]),
FILTER(
ALL(_CALENDAR),
_CALENDAR[Date] <= MAX(_CALENDAR[Date]) &&
_CALENDAR[Date] > EDATE(MAX(_CALENDAR[Date]), -12)) -
With this one, the values, each month decreases Attrition YTD = Calculate( Sum(Attrition[value]),
DATESBETWEEN(
With this one, the same as 2
_CALENDAR[Date],
EDATE(MAX(_CALENDAR[Date]), -11),
MAX(_CALENDAR[Date]))) -- Attrition YTD = Calculate( Sum(Attrition[value]),
Datesinperiod(
_CALENDAR[Date],LASTDATE(_CALENDAR[Date]),-12,MONTH)) -With this one, same as 2 and 3
Any help and assistance would be greatly appreciated,
Regards,
Asa
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.
21 Replies
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
Hi jaineshp and v-sshirivolu , thank you for the solution. I really appreciate it. I have tested it and it does provide the solution of 51. However, it does not follow the expected output as seen in the excel example. The solution should provide all the numbers per month leading up to the 51 at the max month.
Regards,Asa
Hi asa70 ,
Since the earlier approach didn’t return expected results, here’s a refined version using DATESBETWEEN, this should work well for your dynamic 12-month YTD requirement:
AttritionRollingYTD =
CALCULATE(
SUM(Attrition[Value]),
DATESBETWEEN(
_CALENDAR[Date],
EDATE(MAX(_CALENDAR[Date]), -11),
MAX(_CALENDAR[Date])
)
)
MAX(_CALENDAR[Date]) returns the latest reporting month (e.g., Jul 2025). EDATE(..., -11) moves back 11 months (e.g., Aug 2024). DATESBETWEEN covers the full 12-month periodEnsure the visual shows totals for the latest month only, or the results may look cumulative or decreasing. Let me know if you need help with this.
Regards,
Sreeteja.Hi v-sshirivolu , thank you for your solution. The latest month result does show the correct ending value. However, the client wants to see the 12 month view and not just the latest month. It has to match the visual attached.
Regards,
Asa
Hi asa70 ,
Here's what's likely happening with your formulas:
The Issue: Your formulas 2-4 are calculating a rolling 12-month period that shifts with your date context, which explains why values decrease each month.
Quick Fix: Try this approach:
Attrition YTD =
CALCULATE(
SUM(Attrition[value]),
FILTER(
ALL(_CALENDAR[Date]),
_CALENDAR[Date] >= DATE(YEAR(TODAY())-1, MONTH(TODAY())+1, 1) &&
_CALENDAR[Date] <= EOMONTH(TODAY(), 0)
)
)
Alternative (if you want it based on max date in your data):
Attrition YTD =
VAR MaxDate = MAX(_CALENDAR[Date])
VAR StartDate = DATE(YEAR(MaxDate)-1, MONTH(MaxDate)+1, 1)
RETURN
CALCULATE(
SUM(Attrition[value]),
FILTER(ALL(_CALENDAR[Date]),
_CALENDAR[Date] >= StartDate &&
_CALENDAR[Date] <= MaxDate)
)Key Points:
- Use fixed start/end dates rather than relative periods
- The VAR approach gives you more control over the date range
- Make sure your calendar table has continuous dates
This should give you a consistent 12-month window that updates properly each month.
Best Regards,
Jainesh Poojara | Power BI DeveloperHi asa70
i don't think YTD is the appropriate term to use but instead the running 12 months total. If the goal is to show the last x months and not jut the total value, then you will need to use a disconnected table as using a related one will only alter the filter context but not the visible rows. However, if the goal is to compute the total running total for the last x months relative to the current row, please try this:
Total Revenue Last Six Months Running = CALCULATE ( [Total Revenue], DATESINPERIOD ( Dates[Date], MAX ( Dates[Date] ), -6, MONTH ) )Replace 6 with 12.
Please see the attached pbix.
