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

  • AkSaidhana's avatar
    AkSaidhana
    Frequent Visitor
    6 years ago

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