Forum Discussion
Calculating difference between consecutive rows
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.
Hi all,
i manged to solve this using this answer:
basicly i had to calculate for each date the previous date (under the current filters) and then return the result for that date
7 Replies
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
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/339586
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
Hi Anonymous,
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,
DaleHi all,
i manged to solve this using this answer:
basicly i had to calculate for each date the previous date (under the current filters) and then return the result for that date
Thanks for sharing, Anonymous.
Best Regards,
Dale
