Slicer using both a date range and the end date

[brookseitz]

We have an investor dashboard that includes both time series data as well as point in time data. The user will select an investor and a date range and we have charts that show historical performance numbers from the start date to end date coming out of one table (capital) and also show portfolio allocation information as of the end date from another table (portfiolio). The time series data works fine but I'm looking for a way to use the end date in the slicer alone for the point in time data (looks pretty lame if the user has to type in the end date twice on the filter form and gives them the opportunity to generate data that is out of sync). I created a measure to dynamically capture the end date used in the date slicer ( reportDate = CALCULATE(MAX(Capital[End Date]), ALLSELECTED(Capital[End Date]))) ) but can't figure out how to use this value to filter the data in specific charts and graphs from portfolio data. I thought I could create a flag for "IsReportDate" in the portfolio table that could filter all of the rows where the date equals the report date and manually set that filter in the graphs, but couldn't find a way to get that to work.

In the screenshot below, I'd like to be able to filter for rows where the [Date] equals [reportDate]:

Any ideas?

[v-rzhou-msft]

Hi @brookseitz ,

Do you want to filter your visual to show the rows where the [Date] equals [reportDate]? I suggest you to create a measure and add this measure into visual level filter to filter your visual.

Filter =
IF ( MAX ( 'TableName'[Date] ) = [reportDate], 1, 0 )

Add this measure into visual level filter and set it to show items when value =1.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[brookseitz · Accepted solution]

I'll give that a shot. I got around it by calculating a "filtered market value" for the current owner/end date from the slicers, which gives the expected results.

Filtered MV =

// calculate the owner and reporting end date from the slicers

VAR owner = CALCULATE(MAX(Capital[Owner]), ALLSELECTED(Capital[Owner]))

VAR reportDate = CALCULATE(MAX(Capital[End Date]), ALLSELECTED(Capital[End Date]))

RETURN

// sum the market values for rows for the current owner where date equals the reporting end date from slicer

CALCULATE(

SUM(Portfolio[MarketValue]),

ALL(Portfolio[Date]),

FILTER(

Portfolio,

Portfolio[Owner] = owner &&

Portfolio[Date] = reportDate

)

)