Forum Discussion

MattRoed's avatar
MattRoed
Helper I
6 years ago
Solved

Custom Column to Reverse a Running Total

I would like a column that can reverse a column that is keeping track of a running total. 

TrapDateCapturedCountChange
10001/1/202000
10001/8/2020101101
10001/15/202012625
10001/22/202021892
10011/1/202000
10011/8/2020249249
10011/15/2020582333
10011/22/2020696114

This is a sample of my data. Basically I am not able to create the "change" column within Power BI. We have a trap that is reset every 3 weeks. Within the 3 weeks we check the trap once a week to see the total in the trap. I want a clolumn that calculates how much the trap has increased in count each week. Please help! The zero rows signify a setting of a new trap and won't be show in the dashboard. They can be ignored essentially. 

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Please try this expression your column

     

    Change =
VAR __thisvalue = Traps[Count]
VAR __thisdate = Traps[DateCaptured]
VAR __prevdate =
    CALCULATE (
        MAX ( Traps[DateCaptured] ),
        ALLEXCEPT ( Traps, Traps[Trap] ),
        Traps[DateCaptured] < __thisdate
    )
VAR __prevvalue =
    CALCULATE (
        MAX ( Traps[Count] ),
        ALLEXCEPT ( Traps, Traps[Trap] ),
        Traps[DateCaptured] = __prevdate
    )
RETURN
    __thisvalue - __prevvalue

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Please try this expression your column

     

    Change =
VAR __thisvalue = Traps[Count]
VAR __thisdate = Traps[DateCaptured]
VAR __prevdate =
    CALCULATE (
        MAX ( Traps[DateCaptured] ),
        ALLEXCEPT ( Traps, Traps[Trap] ),
        Traps[DateCaptured] < __thisdate
    )
VAR __prevvalue =
    CALCULATE (
        MAX ( Traps[Count] ),
        ALLEXCEPT ( Traps, Traps[Trap] ),
        Traps[DateCaptured] = __prevdate
    )
RETURN
    __thisvalue - __prevvalue

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • MattRoed's avatar
      MattRoed
      Helper I
      6 years ago

      No luck with this solution. I'm very new to PowerBI so it may be a user error. The "Trap" column is actually titled "QRCode" and the "Count" column is "BugCount". Do you think you could update the formula with these changes so I could possibly just copy and paste? The "Table" name is "ifly_info". I also am getting an error that "VAR" does not exist. 

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    MattRoed , as new columns

    last date = maxx(filter(Table, Table[Trap] = earlier(Table[Trap]) && Table[DateCaptured] < earlier(Table[DateCaptured])),Table[DateCaptured])

Change = Table[Change] - maxx(filter(Table, Table[Trap] = earlier(Table[Trap]) && Table[DateCaptured] = earlier(Table[last date])),Table[Change])

     

    As new measure

    Last Day Non Continous = CALCULATE(sum('Table'[Count]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),'Table'['DateCaptured'])))

Change = sum('Table'[Count])- [Last Day Non Continous]
    • MattRoed's avatar
      MattRoed
      Helper I
      6 years ago

      amitchandak I was able to get your first column to work, the last date one. But got an error when trying to make the change column. I don't think it liked that you put the change column inside of the formula itself. Any thoughts on how to overcome this?

  • MattRoed's avatar
    MattRoed
    Helper I
    6 years ago

    amitchandak your new column worked initially, but I had to re import my dataset and now it seems to be out putting negative values. Any thoughts on how to fix this?