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

Hi Casperserven1 
Is this a data table or a table visual? How did you get the first value (22)?

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!