Forum Discussion
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...
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 - __prevvalueIf this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
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
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.
