Calculate YTD based on multiple columns and dynamic month

[Anonymous]

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

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[Anonymous]

Thank you.  I had to adjust my data and then your solution worked.

[v-jiascu-msft]

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

[AkSaidhana]

Hey experts,  many thank you.... ur sharing solved my 4 days work.

[Anonymous]

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]

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