Forum Discussion
Power Query - compare last two records.
- 3 years ago
Anonymous , Split column one create country and date column
Then you can have measures like
Current =
var _max = maxx(allselected(Table), Table[Country] = max(Table[Country]) ), Table[Date])
return
calculate(sum(Table[Value]), filter(Table, Table[Date] =_max) )
Previous =
var _max1 = maxx(allselected(Table), Table[Country] = max(Table[Country]) ), Table[Date])
var _max = maxx(allselected(Table), Table[Country] = max(Table[Country]) && Table[Date] <_max1 ), Table[Date])
return
calculate(sum(Table[Value]), filter(Table, Table[Date] =_max) )
We can also explore index function
with partition by country order by date desc
current=
calculate(sum(Table[Value]), index(1, allselected(Table[Country], Table[Date]), orderBy(Table[Date], desc) ,partitionBy(Table[Country])))
prior=
calculate(sum(Table[Value]), index(2, allselected(Table[Country], Table[Date]), orderBy(Table[Date], desc) ,partitionBy(Table[Country])))
Anonymous , Split column one create country and date column
Then you can have measures like
Current =
var _max = maxx(allselected(Table), Table[Country] = max(Table[Country]) ), Table[Date])
return
calculate(sum(Table[Value]), filter(Table, Table[Date] =_max) )
Previous =
var _max1 = maxx(allselected(Table), Table[Country] = max(Table[Country]) ), Table[Date])
var _max = maxx(allselected(Table), Table[Country] = max(Table[Country]) && Table[Date] <_max1 ), Table[Date])
return
calculate(sum(Table[Value]), filter(Table, Table[Date] =_max) )
We can also explore index function
with partition by country order by date desc
current=
calculate(sum(Table[Value]), index(1, allselected(Table[Country], Table[Date]), orderBy(Table[Date], desc) ,partitionBy(Table[Country])))
prior=
calculate(sum(Table[Value]), index(2, allselected(Table[Country], Table[Date]), orderBy(Table[Date], desc) ,partitionBy(Table[Country])))
- Anonymous3 years agoNot applicable
Thank you amitchandak !