Get Previous row record dynamically

[sree_12]

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 Table2 has Yearmonth even if the students/ subjects that not scheduled 

Table1:

Year Month	Subject	Student- ID
Dec 2020	Physics	A
Dec 2020	Physics	B
Nov 2020	Physics	B
Oct 2020	Chemistry	A
Oct 2020	Maths	C
Sep 2020	Physics	A
Sep 2020	Chemistry	B

Table2:

Year Month

Mar 2021

Dec 2020

Nov 2020

Oct 2020

Maintaing Many to many relationship based on the data I have

I was able to get max record if I use KPI and even if I filter "year month", I was able to display students attended on max date.

Most recent= calculate (count(Student- ID), filter (Table1, Table1[Year Month]= max(Table2[Year Month])))

But, the problem I am seeing is when I use a bar graph or matrix and use "Subject" on Rows and count(Student -ID) on Values section.

The values are not populating properly, for instance If I  dont select "Year month"

The KPI displays total as 0, as we dont have any data in Mar 2021.

But in the matrix the values are displaying based on subject max year month 

Here Physics max year month is Dec 2020 and getting 2 as count for recent month

Chemistry is considering Oct 2020 as most recent month and showing 1 as count

Maths - Oct 2020 is most recent and 1 as most recent student count

Subject	Recent Month	Previos Month
Physics	2	1
Chemistry	1	1
Maths	1	0

Expected result: 

Results I am expecting when no filters selected then recent month Mar 2021 : 0 for all subjects and previous month (Dec 2020) Physics is 2 and  chemistry and maths -0,

display 0 in recent month and in previous month display values for Dec 2020 (One of measure I created is considering wrongly Feb 2021 data as previous month - where it doesn't exist)

Subject	Recent Month	Previos Month
Physics	0	1
Chemistry	0	0
Maths	0	0

And, when filter applied "Nov 2020" 

display records for Nov 2020 as recent month and Oct 2020 as previous month dynamically

I appreciate your time and Please suggest me dax/ solution to achieve this.

Thank you,

[sree_12]

Thanks @amitchandak this is what I am doing since two days but I made small mistake in my previous calculation, which throws error

But now everything works perfectly

Thanks!

[amitchandak · Accepted solution]

@sree_12 , 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))