Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Get Previous row record dynamically

Hello Everyone,   I need your help in writing dax for getting most recent and previous row record that changes dynamically based on filter selection. I have two tables Table1 has student data and ...
  • amitchandak's avatar
    5 years ago

    Anonymous , I am assuming table two unique year month table.

    Create a new column in both tables

     

    Date = "01-" & [Month] & "-" & [year] // Change data type to date. 

    Join both 1-M from Table 2 to table

     

    Create a new column in table 2

    Month Year sort = FORMAT([Date],"yyyymm")
    Month Rank = RANKX(ALL('Table2'),'Table2'[Month Year Sort],,DESC,Dense)

     

     

    Try measure like these example

     

    This Month = CALCULATE(count('Table1'[Student- ID]), FILTER(ALL('Table2'),'Table2'[Month Rank]=max('Table2'[Month Rank])))
    Last Month = CALCULATE(count('Table1'[Student- ID]), FILTER(ALL('Table2'),'Table2'[Month Rank]=max('Table2'[Month Rank])-1))