Custom Column to Reverse a Running Total

[MattRoed]

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	126	25
1000	1/22/2020	218	92
1001	1/1/2020	0	0
1001	1/8/2020	249	249
1001	1/15/2020	582	333
1001	1/22/2020	696	114

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. 

[MattRoed]

@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?

[amitchandak]

@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]

@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?

[mahoneypat · Accepted solution]

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]

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.