Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

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 https://1drv.ms/x/s!AtC4vPlx9PrghywunoTDZiy1b0dy

Need Help

v-jiascu-msft
7 years ago
Hi Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

v-jiascu-msft

Microsoft Employee

7 years ago

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

Calculate YTD based on multiple columns and dynamic month.pbix86 KB

Anonymous

Not applicable

7 years ago

I 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

Microsoft Employee

7 years ago

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