Forum Discussion
Calculating difference/change from the previous date or version.
Hi,
I'm struggling finding the difference of value between dates. They are irregular and aren't monthly or yearly.
My desired outcome would be a measure that calculates difference and change of the value.
something that looks like difference column
thanks
| Code | region | test date | score | difference |
| A | US | 05/07/19 | 90 | 0 |
| A | US | 09/08/19 | 130 | 40 |
| A | CA | 10/07/19 | 50 | 0 |
| B | US | 05/07/19 | 80 | 0 |
| B | US | 09/08/19 | 70 | -10 |
| B | CA | 10/07/19 | 75 | 0 |
Anonymous , Try as new columns
last date = maxx(filter(Table, Table[Code] = earlier(Table[Code])&& Table[region] = earlier(Table[region]) && Table[Date] < earlier(Table[Date])),Table[Date]) difference = Table[score] - maxx(filter(Table, Table[Code] = earlier(Table[Code])&& Table[region] = earlier(Table[region]) && Table[Date] = earlier(Table[last date])),Table[score])As new measures
Last Day Non Continous = CALCULATE(sum('Table'[score]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),'Table'['DAte']))) Change = sum('Table'[score])- [Last Day Non Continous]
2 Replies
- amitchandak
Super User
Anonymous , Try as new columns
last date = maxx(filter(Table, Table[Code] = earlier(Table[Code])&& Table[region] = earlier(Table[region]) && Table[Date] < earlier(Table[Date])),Table[Date]) difference = Table[score] - maxx(filter(Table, Table[Code] = earlier(Table[Code])&& Table[region] = earlier(Table[region]) && Table[Date] = earlier(Table[last date])),Table[score])As new measures
Last Day Non Continous = CALCULATE(sum('Table'[score]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),'Table'['DAte']))) Change = sum('Table'[score])- [Last Day Non Continous] - AllisonKennedy
Community Champion
You could try doing this as a column using EARLIER and RANK if you want the difference between the current and previous date for each Code > Region.
