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

Casperserven1's avatar
Casperserven1
Helper I
3 years ago

and this is the raw data.

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Casperserven1 
Please refer to attached sample file with the proposed solution

Total Workforce = 
VAR StartingWF = 22
VAR TableOnAndBefore = 
    FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )
VAR Change = 
    SUMX ( TableOnAndBefore, 'Table'[New Hires] - 'Table'[Retirees] )
RETURN
    StartingWF + Change
  • Casperserven1's avatar
    Casperserven1
    Helper I
    3 years ago

    Thanks the pbix worked perfectly, however when I tried the technique with other data this is what occurred...any ideas??

    • 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.