Forum Discussion

jake223's avatar
jake223
Helper I
6 years ago

Cumulative/Running Total

Hello,   I'm having an issue with creating a Cumulative/Running Total. Currently I have the following:   StartFTE_TO = sum('92_FOM_FY20'[Current FTE for TO]) EndFTE_TO = SUM('92_FOM_FY20'[Curr...
Need Help
ImkeF's avatar
ImkeF
Community Champion
6 years ago

Hi jake223 ,

the problem lies in your matrix visual. Once you pull a column that indicates the year into the rows section, the values should turn out fine. As there is probably more than one June in your calendar table, it is not clear which year is meant - and therefore the order cannot be determined.

 

  • jake223's avatar
    jake223
    Helper I
    6 years ago

    Hi ImkeF 

     

    I updated my date table From Start Date: 6/1/2016 To: 6/1/2019 the start of our fiscal year (June in the Image). and it still seems it only calculating the current month. But I do a think it might have to do with using Fiscal year just not sure how/what/why. 

     

     

     

    Thanks for all your Help!

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      Seem like it is doing Avg. Can you share the formula.

      • jake223's avatar
        jake223
        Helper I
        6 years ago

        Hi amitchandak 

         

        I used the Quick measure to create both fields below are the formulas

         

        • Turnover running total in FiscalMonthLong = CALCULATE([Turnover],FILTER(CALCULATETABLE(SUMMARIZE('7_Date', '7_Date'[FiscalMonthNum], '7_Date'[FiscalMonthLong]),ALLSELECTED('7_Date')),ISONORAFTER('7_Date'[FiscalMonthNum], MAX('7_Date'[FiscalMonthNum]), DESC,'7_Date'[FiscalMonthLong], MAX('7_Date'[FiscalMonthLong]), DESC)))
        • Turnover YTD = TOTALYTD([Turnover], '7_Date'[Date])