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,

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

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

     

    Regards