Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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) piv...
  • amitchandak's avatar
    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])))