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
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])
amitchandak's avatar
amitchandak
Super User
6 years ago

Calculation of turnover

  • jake223's avatar
    jake223
    Helper I
    6 years ago

    amitchandak 

     

    Turnover = ([TermsFTE_TO] / (([StartFTE_TO]+[EndFTE_TO]) / 2))

    • tex628's avatar
      tex628
      Community Champion
      6 years ago

      Hello jake223 ,

      Try changing from:

      Cumulative = CALCULATE([Turnover], FILTER(ALLSELECTED('7_Date'), '7_Date'[Date] <= MAX('7_Date'[Date])))

       

      to 

      Cumulative =
      VAR maxDate = MAX('7_Date'[Date])
      Return
      CALCULATE([Turnover], ALL('7_Date'), '7_Date'[Date] <= maxDate))

      Br,
      J

      • jake223's avatar
        jake223
        Helper I
        6 years ago

        tex628 

         

        I tried using and looks like that same result

         

        Cumulative =
        VAR maxDate = MAX('7_Date'[Date])
        RETURN
        CALCULATE([Turnover], ALL('7_Date'), '7_Date'[Date] <= maxDate)