Use the max value of a filter to filter a visual

[MRD87]

Hello all,

I have spent a lot of time in figuring out a solution for this. 

I am using a filter where I select a week. I want the visual to not show the results after the max date of the selected week. 

So instead of this

I want this result

I have a revenue and a date table which are related by date.  I hope someone can help me figure this out. 

[MRD87]

Thank you this helped me out tremendously

[johnt75 · Accepted solution]

Create a copy of your date table just for use in the slicer

Slicer Date = SELECTCOLUMNS( 'Date', "Date", 'Date'[Date], "Week", 'Date'[Week])

Do not link this table to any other tables. Then you can create a measure like

Filtered Revenue = IF( MAX('Date'[Date]) <= MAX('Slicer date'[Date]), [Revenue CY])

and use that in your visual

[MRD87]

Slicer Date = SELECTCOLUMNS( 'Date Slicer', "Date", 'Date Slicer'[Date], "Week", 'Date Slicer'[Week])

I get the following error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[johnt75]

You need to add it as a New Table, not a column or a measure

[MRD87]

Thank this brought me a little bit further in the right direction. I am almost there. I had to change the <= into >= for the measurements to work. Now I get the right weeks, but every week shows the revenue of the selected week ( week 23 in this case). Is there a solution for this?

Thank you in advance

[johnt75]

what code are you using for the Filtered Revenue measure ?

[MRD87]

Filtered Revenue = IF( MAX('Date'[Date]) >= MAX('Slicer date'[Date]),[Revenue CY])

[johnt75]

that should be <= not >=, and you need to use the week from your date table, not the slicer date, on the column chart

[MRD87]

Thank you this works, however I have one last challenge.  I want both datetables to be filtered with one slicer.  This is not possible with a relation because that would nullify the result. Is there a way to filter both slicers with the same value (week)?

[johnt75]

If a filter were applied to the date table that would break the calculation that we have working now.