Calculating difference between consecutive rows

[ellevy12]

hello

i am new to power bi , and i have some struggle with this. 

my data look like this (small portion:)

DATE / sum

2018-07-01 / 200

2017-09-03 / 400

2016-19-04 / 500

i want to calculate the difference between those dates , so i want to create new column:

DATE / sum / new column

2018-07-01 / 200    /    400

2017-09-03 / 400   /    500

2016-19-04 / 500 /   

only problems on the data sets: 

1. dates are not strictly consecutive , meaning i can have 5 dates in 2018 and 10 dates in 2017 

2. i want those calculation to be filtred by the user. meaning - if user deselect the '2017-09-03' date , then calculation will changed to the following:

DATE / sum / new column

2018-07-01 / 200    /    500

2016-19-04 / 500 /   

thanks.

[v-jiascu-msft]

Hi @ellevy12,

Please provide a sample of the original data. Since the dates aren't in order by dates, how can we determine which one is former, which one is latter?

Best Regards,
Dale

[ellevy12 · Accepted solution]

Hi all,

i manged to solve this using this answer:

Answer

basicly i had to calculate for each date the previous date (under the current filters) and then return the result for that date

[v-jiascu-msft]

Thanks for sharing, @ellevy12.

Best Regards,
Dale

[Greg_Deckler]

Up until that last requirement, you could have just used a calculated column with EARLIER.

Now, you will need a measure and use a VAR to grab your current table (as filtered by user). Then, use ADDCOLUMNS to add a column using EARLIER to that table and do your calculations.

Here is an article on the use of EARLIER:

See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[ellevy12]

sorry i think i mix things up.. actualy what i reffered to as my data is actualy a visual matrix / table  based on the data.

does this change somthing ?

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[LivioLanzo]

You cannot do this by using a Calculated Column but you can do it using a Measure. Is a measure ok?   Also can you include in the dataset you have provided any column that is relevant for the calculation