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 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's avatar
    v-sshirivolu
    Community Support
    1 year ago

    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's avatar
      asa70
      Advocate I
      1 year ago

      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's avatar
        v-sshirivolu
        Community Support
        1 year ago

        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