Forum Discussion
Calculate YTD based on multiple columns and dynamic month
I would like to calculate running year to date total based on dynamic fiscal period and columns as per attached file
Hi Anonymous,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
6 Replies
v-jiascu-msftMicrosoft Employee
Hi Anonymous,
Try the demo in the attachment. I hope you didn't share any sensitive data here.
1. Create a date table;
2. Establish a relationship;
3. Create a measure.
Measure = TOTALYTD ( SUM ( Table1[Amount] ), 'Calendar'[Date] )
Best Regards,
DaleI need to be dynamic for example if there is a forecast version with data till the end of the year and actual version is only till October. Then YTD amount for forecast should be until October for all projects. I am basically looking for a column with YTD running total to be pulled into the report bcos these projects are later grouped differentlyy.
- v-jiascu-msft
Hi Anonymous,
How about this one? The calculated column may not be a good idea.
Measure 2 = VAR maxDate = CALCULATE ( MAX ( Table1[Fiscal year/period|Key Figures] ), FILTER ( ALL ( 'Table1' ), 'Table1'[VERSION] = "Actual" ) ) RETURN TOTALYTD ( SUM ( Table1[Amount] ), 'Calendar'[Date], 'Calendar'[Date] <= maxDate )Best Regards,
Dale
Hey experts, many thank you.... ur sharing solved my 4 days work.
- v-jiascu-msft
Hi Anonymous,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
