Filter Matrix Table by Relative Value to DAX Dynamic Date Rank Measure
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
