Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I need to create a calculated column that subtracts the current row value from the previous row value based on date and customer name. For example:
Date | Value | Customer | Calc_column (expected value) |
01-04-2020 | 100 | A | |
01-04-2020 | 400 | B | |
01-04-2020 | 500 | C | |
02-04-2020 | 120 | A | 20 |
02-04-2020 | 440 | B | 40 |
02-04-2020 | 560 | C | 60 |
Thanks in advance!
Solved! Go to Solution.
Hi @ashrafkotb
try
Calc_column =
var _thisDate = Table[Date]
var _prevDate = CALCULATE(MAX(Table[Date]), ALLEXCEPT(Table, Table[Customer]), Table[Date] < _thisDate )
RETURN
Table[Value] - CALCULATE(MAX(Table[Value]), ALLEXCEPT(Table, Table[Customer]), Table[Date] = _prevDate )
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
So in your case, something like:
Calc_column =
VAR __PreviousDate = MAXX(FILTER('Table',[Date] < EARLIER([Date]) && [Customer] = EARLIER([Customer])),[Date])
VAR __PreviousValue = MAXX(FILTER('Table',[Date] = __PreviousDate) && [Customer] = EARLIER([Customer])),[Date])
RETURN
[Value] - __PreviousValue
Getting a Syntax error for this formula.
Try new columns like
max date = maxx(filter(Table, table[Date]<earlier(Table[Date]) && table[Customer]=earlier(Table[Customer])),Table[Date])
diff = Table[value] - maxx(filter(Table, table[Date]=earlier(Table[max date]) && table[Customer]=earlier(Table[Customer])),Table[value])
Hi @ashrafkotb
try
Calc_column =
var _thisDate = Table[Date]
var _prevDate = CALCULATE(MAX(Table[Date]), ALLEXCEPT(Table, Table[Customer]), Table[Date] < _thisDate )
RETURN
Table[Value] - CALCULATE(MAX(Table[Value]), ALLEXCEPT(Table, Table[Customer]), Table[Date] = _prevDate )
This worked, thank you.