Forum Discussion
Calculate Daily Differences Based on Dates and Changing Province
Community Championfarhanerd 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/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
Hi Greg_Deckler, I tried to put that query on my table but the result seems doesn't as what I expected.
Am I put the query wrong?
I'm kinda new here and after lots and lots of trying attempts I still didn't get the answer I want, I need your guidance Greg_Deckler
- Greg_Deckler
farhanerd I am guessing that there is some kind of identifier that identifies which rows are "grouped" together. For example, I see a bunch of March 16th rows in your data. Thus, I am guessing that there are probably multiple March 15th rows. The code you have will find March 15th for the Previous Date. But, it will then look up the largest value on that date, which is apparently 210. So, if you were looking for 117 instead I think you will need to add more specificity to your FILTER statemetns like && [somecolumn] = EARLIER([somecolumn)
Note that you can also use VAR statements to grab your current values instead of using EARLIER.