Forum Discussion

Casperserven1's avatar
Casperserven1
Helper I
3 years ago
Solved

Accumulating using previous

I am trying to do tthe following and struggling - I am sure this is easy but just can't see it. New Hires Retirees Total Workforce 3 5 20 6 3 23 8 1 30 9 8 31 4 6 ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Casperserven1

    Now I got it. Please try

    Total Workforce =
    VAR StartingWF = 22
    VAR TableBefore =
    FILTER ( 'Table', 'Table'[Date] < EARLIER ( 'Table'[Date] ) )
    VAR Change =
    SUMX ( TableBefore, 'Table'[New Hires] - 'Table'[Retirees] )
    RETURN
    StartingWF + Change

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Casperserven1 
It should be a calculated column not a measure!

Casperserven1's avatar
Casperserven1
Helper I
3 years ago

Thankyou so much , unfortunately it still isn't right. We only want the starting figure of 22 in the first row. after that is thould be adding and subtracting from the previous row.

 

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Casperserven1 
    Please clarify further. Perhaps with a screenshot?

    • Casperserven1's avatar
      Casperserven1
      Helper I
      3 years ago

      yeah sure

       

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Casperserven1

        Now I got it. Please try

        Total Workforce =
        VAR StartingWF = 22
        VAR TableBefore =
        FILTER ( 'Table', 'Table'[Date] < EARLIER ( 'Table'[Date] ) )
        VAR Change =
        SUMX ( TableBefore, 'Table'[New Hires] - 'Table'[Retirees] )
        RETURN
        StartingWF + Change