Forum Discussion

Anonymous's avatar
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's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

    Could you please mark the proper answers as solutions?

     

    Best Regards,

    Dale

Anonymous's avatar
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's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft 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
    )

Calculate-YTD-based-on-multiple-columns-and-dynamic-month2

 

Best Regards,
Dale