Forum Discussion
Dynamic 12 Month YTD Calculation
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.
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.
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
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.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.
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
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.
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 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