Dynamic 12 Month YTD Calculation

[asa70]

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:

1. 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.
2. 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

3.  Attrition YTD = Calculate( Sum(Attrition[value]), 

   DATESBETWEEN(
           _CALENDAR[Date],
           EDATE(MAX(_CALENDAR[Date]), -11),
           MAX(_CALENDAR[Date]))) -

   With this one, the same as 2
4. 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

[asa70]

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

[v-sshirivolu]

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 period

Ensure 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.

[asa70]

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

[v-sshirivolu]

Hi @asa70 ,

Create a Date Table
DateTable = CALENDAR(DATE(2023,1,1), DATE(2025,12,31))

Create a Relationship
Connect DateTable[date] - your data table's Month column

Create the Measure
Attrition_12M_TillMax =
VAR CurrentMonth = MAX('YourTable'[Month])
RETURN
CALCULATE(
SUM('YourTable'[Attrition]),
DATESINPERIOD('DateTable'[Date], CurrentMonth, -12, MONTH)
)

Please find attached .pbix file for your reference.

Regards,
Sreeteja

[v-sshirivolu]

Hi @asa70 ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[v-sshirivolu]

Hi @asa70 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[asa70]

Hi @v-sshirivolu, I have reviewed the information that you provided. Thank you so much. Upon reveiwing, I noticed that the example data has only 12 months and the example I shown has more than 12 months. How do we then ensure that the solution works for when there is more than 12 months of data present? So the solution provided still does not answer my problem.

Kind Regards,

Asa

[v-sshirivolu]

Hi @asa70 ,

My previous example used just 12 months, so it worked for a single year. For data across multiple years, use a rolling 12-month measure with DATESINPERIOD instead of DATESBETWEEN.

AttritionRolling12M =
CALCULATE(
   SUM(Attrition[Attrition]),
   DATESINPERIOD(
       '_Date'[Date],
       MAX('_Date'[Date]),
       -12,
       MONTH
   )
)

This method uses DATESINPERIOD to create a rolling 12-month window, always ending with the current month. It smoothly handles year changes and ensures the measure shows the correct total for the latest 12 months, no matter how many years of data there are.

[asa70]

Hi @v-sshirivolu , thank you so much. However, this solution has already been presented and I also have responded with what happens when I use the solution. It does not work. The closest solution that has worked is (which I have already explained in the problem statement):

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.

The solution must match the attached screenshot example.

Regards,

Asanele

[v-sshirivolu]

Hi @asa70 ,
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.

[asa70]

Hi @v-sshirivolu , as per request, please see below table for a small sample with the expected outcome next to it.

Hope that helps!

Kind Regards,

Asa

Month Year	Attrition	12 Month To Date Attrition
Jan-24	9	115
Feb-24	2	117
Mar-24	2	119
Apr-24	11	130
May-24	10	140
Jun-24	3	143
Jul-24	8	151
Aug-24	6	6
Sept-24	2	8
Oct-24	9	17
Nov-24	4	21
Dec-24	2	23
Jan-25	7	30
Feb-25	3	33
Mar-25	0	33
Apr-25	2	35
May-25	8	43
Jun-25	0	43
Jul-25	5	48

[v-sshirivolu · Accepted solution]

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]

Hi @v-sshirivolu , thank you so much for the solution. I have viewed and tested it on my end. It works as expected!

Kind Regards,

Asa

[v-sshirivolu]

Hi @asa70 ,

I wanted to check if you had the opportunity to review the information provided by @jaineshp . Please feel free to contact us if you have any further questions.

Thank you and continue using Microsoft Fabric Community Forum.

[asa70]

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]

Hey @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

[asa70]

Hi @jaineshp , thank you for the updated solution. Unfortunately, it goes back to the original problem I was having.

Regards

[jaineshp]

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

[jaineshp]

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!

[danextian]

Hi @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.