Forum Discussion
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 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,
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))
2 Replies
- amitchandak
Super User
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)) 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!