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 ...
Dax Help
  • amitchandak's avatar
    amitchandak
    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))

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
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))