Filter Matrix Table by Relative Value to DAX Dynamic Date Rank Measure

[wifc5013]

Hi All,

I have successfully created a RANK measure (DateRank) thanks to powerBI forum that will dynamically adjust with slicer selections. I must now create a table that will respond relative to that Rank, specifically, MIN(DateRank)+1 or DateRank = 2.

DateRank =

MINX (

FILTER (

SELECTCOLUMNS (

ALLSELECTED ( 'Table' ),

"index", 'Table'[Index],

"rank", RANKX ( ALLSELECTED ( 'Table' ), 'Table'[Report_Date],, DESC, DENSE )

),

[index] = MAX ( [index] )

),

[rank]

)

This is due to a report which will only run certain days of the week (not weekends). Report_Date corresponds to the report output of the specified date, and when selecting a Report_Date through a slicer, we would like to affect two tables: one which displays selected Report_Date PV (present value) and one that displays the immediately preceeding Report_Date PV. 

For the supplied PBI data, when 12/14/21 is selected on the filter, the "Selected Date" table will display PV as of 12/14/21, and the "Previous Date" table will display PV as of 12/14/21.

Using the DateRank measure, I have tried to filter "Previous Date" on DateRank = 2 using Filter pane, and also a measure [PV_Yesterday] where DateRank = 2, but niether are working. I would prefer to Filter on DateRank = 2 or an equivalent solution, but if new measure is required like [PV_Yesterday] instead of a visual filter, then I am open to that. Thank you for considering this problem!

Report_Date	REGION	PV
10/25/2021 12:00:00 AM	MID-WEST	-1584657.012
10/25/2021 12:00:00 AM	NORTH-EAST	47711338.72
10/25/2021 12:00:00 AM	SOUTH-EAST	0
12/14/2021 12:00:00 AM	SOUTH-EAST	0
11/10/2021 12:00:00 AM	NORTH-EAST	-2920107.499
11/10/2021 12:00:00 AM	MID-WEST	0
11/29/2021 12:00:00 AM	NORTH-EAST	-15895241.11
12/6/2021 12:00:00 AM	MID-WEST	0
12/9/2021 12:00:00 AM	MID-WEST	0
11/11/2021 12:00:00 AM	NORTH-EAST	0
11/11/2021 12:00:00 AM	MID-WEST	0
12/8/2021 12:00:00 AM	NORTH-EAST	2.015367962
12/1/2021 12:00:00 AM	NORTH-EAST	0
11/17/2021 12:00:00 AM	NORTH-EAST	0
11/1/2021 12:00:00 AM	NORTH-EAST	-1160.720229
10/28/2021 12:00:00 AM	NORTH-EAST	0
11/5/2021 12:00:00 AM	NORTH-EAST	-1033717.772
11/23/2021 12:00:00 AM	MID-WEST	0
11/23/2021 12:00:00 AM	NORTH-EAST	134.1285225
11/8/2021 12:00:00 AM	NORTH-EAST	0
11/22/2021 12:00:00 AM	MID-WEST	0
11/19/2021 12:00:00 AM	NORTH-EAST	-798412.6336
11/19/2021 12:00:00 AM	SOUTH-EAST	0
11/16/2021 12:00:00 AM	MID-WEST	0
11/15/2021 12:00:00 AM	MID-WEST	0
11/12/2021 12:00:00 AM	NORTH-EAST	0
11/12/2021 12:00:00 AM	MID-WEST	0
11/9/2021 12:00:00 AM	NORTH-EAST	2.193014276
12/14/2021 12:00:00 AM	NORTH-EAST	-11874298.76
11/19/2021 12:00:00 AM	MID-WEST	0
11/10/2021 12:00:00 AM	SOUTH-EAST	0
11/16/2021 12:00:00 AM	NORTH-EAST	-140.1228899

[v-luwang-msft · Accepted solution]

Hi @wifc5013 ，

"when 12/14/21 is selected on the filter, the "Selected Date" table will display PV as of 12/14/21, and the "Previous Date" table will display PV as of 12/14/21",all return the same date value?

If you understand it from something else, you want to return the pv value corresponding to the date selected by the slicer, and the pv value corresponding to the previous date selected by the slicer.

Like below:

I provide my pbix file ,and you could download to learn details.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien