cancel
Showing results for
Did you mean:
Helper I

## How to create a calculated column to subtract previous row value?

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

1 ACCEPTED SOLUTION
Super User

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 )``````

do not hesitate to give a kudo to useful posts and mark solutions as solution
5 REPLIES 5
Super User

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
``````

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helper I

Getting a Syntax error for this formula.

Super User

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

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Super User

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 )``````

do not hesitate to give a kudo to useful posts and mark solutions as solution
Helper I

This worked, thank you.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!