Forum Discussion
Power Query - compare last two records.
I have a worksheet with 20+ countries, and each country has multiple dates. I want to grab the last two dates for each country and compare them. Here is my basic plan:
1) group by country
2) pivot by country
The challenge is how I can only select the latest two dates. One country may have 3 dates (rows), and another one may have 5 dates. Each country will vary in the number of rows.
Please advise. Thank you!
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])))
2 Replies
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])))Thank you amitchandak !