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.

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

v-sshirivolu's avatar
v-sshirivolu
Community Support
1 year ago

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

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

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

        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.