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