[EDIT 2020-07-13 13:55 UK time]
// Assumption:
// You have a calendar table with all the dates
// you need. This table MUST be disconnected.
// Then you create a measure that will, for each row
// in the table you've presented, return 1 if the
// row qualifies according to your requirement, and 0/Blank
// if not. Once you have this measure, you can drop
// the fields from the table into a table/matrix visual
// and filter the rows in the Filter pane via the measure.
// When it's value is 1, show the row. Hide otherwise.
// Also, we are assuming that each combination of (Member, Product, RN)
// cannot have more than one Report Date assigned.
// Here's the measure:
Filtering Measure =
var __shouldCalc =
HASONEFILTER( T[Report Date] )
&& HASONEFILTER( T[Member] )
&& HASONEFILTER( T[Product] )
&& HASONEFILTER( T[RN] )
var __result =
IF( __shouldCalc,
var __selectedDate = SELECTEDVALUE( 'Calendar'[Date] )
var __currentRN = SELECTEDVALUE( T[RN] )
var __currentMember = SELECTEDVALUE( T[MEMBER] )
var __currentProduct = SELECTEDVALUE( T[PRODUCT] )
// For each combination of (Member, Product, Report Date)
// we have to find out if the combination is what we're
// looking for.
var __shouldRetain =
CALCULATE(
MAXX(
// This one picks up the row from the
// table with the latest date, respecting
// all the filters under CALCULATE. MAXX
// picks up the one and only value of RN
// that qualifies for our condition.
TOPN(
1,
T,
T[Report Date],
DESC
),
T[RN]
// The "=" condition returns true if the current
// RN is equal to the one that we pick up among
// the RN's for the same Member and Product. If
// it's equal, then we know we're on the row
// that should be returned.
) = __currentRN,
// This makes sure that we're only looking at
// rows in the table that have Report Date
// before or on the selected date.
KEEPFILTERS( T[Report Date] <= __selectedDate ),
// This one makes sure that for each row in the visual
// we're only looking at the rows that have the same
// Member and Product as the current row.
ALLEXCEPT( T, T[Member], T[Product] ),
// Filter only the dates that are visible for the
// current Member and Product.
CALCULATETABLE(
VALUES( T[REPORT DATE] ),
FILTER(
ALLSELECTED( T ),
T[MEMBER] = __currentMember
&&
T[PRODUCT] = __currentProduct
)
)
)
return
__shouldRetain
)
return
1 * __result
Best
D
