cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sree_12
Frequent Visitor

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 MonthSubjectStudent- ID
Dec 2020PhysicsA
Dec 2020PhysicsB
Nov 2020PhysicsB
Oct 2020ChemistryA
Oct 2020MathsC
Sep 2020PhysicsA
Sep 2020ChemistryB

 

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 MonthPrevios Month
Physics21
Chemistry11
Maths10

 

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 MonthPrevios Month
Physics01
Chemistry00
Maths00

 

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,

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
sree_12
Frequent Visitor

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
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors