Forum Discussion

Casperserven1's avatar
Casperserven1
Icon for Helper I rankHelper 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
Icon for Community Champion rankCommunity 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
Icon for Helper I rankHelper 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
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Casperserven1 
    It should be a calculated column not a measure!

    • Casperserven1's avatar
      Casperserven1
      Icon for Helper I rankHelper 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
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Casperserven1 
        Please clarify further. Perhaps with a screenshot?