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.  Trap DateCaptured Count Change 1000 1/1/2020 0 0 1000 1/8/2020 101 101 1000 1/15/2020...
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

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?