Dynamic filter depending on column

[_Xandyr_]

Hello,

Im creating a report where I need a visual to filter on a specific column (a date, Today) and this date will change every month and therefore the matrix need to change accordingly.

Below I try to show you what I mean, the input data will change every month but the filter should be based on the column Today and only summarize rows in the Date column after Today. I hope i makes sense.

Thank you!

[V-lianl-msft]

Hi @_Xandyr_ ,

Based on your description, you want to display data after today in chart.
You can create a measure to get today's date: today = TODAY()

Create another measure to compare dates: comparison = IF(MAX(F_Sales[date])>[today],1,0)
Then add the comparison result to visual as a filter

You can also refer to this pbix

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

[_Xandyr_]

@V-lianl-msft  Hello, thank you for the tips. However the column Today is not todays date but instead the latest date this report was refreshed (my bad with the naming but its the actual name in the report...).

So the column Today will be different each month depending on what day the input data was retrieved. Which means the TODAY() wont work here.

Thanks!

[V-lianl-msft]

Hi @_Xandyr_ ,

To display the date and time of the refresh, we need to store the date and time of the time refresh run in the model itself so that we can display it in visual.

You can refer to the pbix

Best Regards,

Liang

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

[_Xandyr_]

Hi again @V-lianl-msft,

Im not sure Im following. I already have the time of refresh in my column named Today. Then what I want to display in my visuals are data that happens after the date in the column Today.

For example if the column Today says 3rd of december 2019 I only want to show data later than 3rd of december. If the column Today says 7th of december I only want to show data later than 7th of december and so on.

Thanks!

[V-lianl-msft · Accepted solution]

Hi @_Xandyr_ ,

Again,please refer to the pbix.

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

[_Xandyr_]

@V-lianl-msft  Hi agian,

Thank you for this, should work!

The solution was the following

Create measure like this

Measure = IF(MAX(F_Sales[date])>MAX(F_Sales[Custom]),1,0)

where F_Sales Date = Date in my first post

where F_Sales Custom = Today in my first post.

In your visual add comparison to your filter and check 1.