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