How add dynamic filter value (for date )

[dainova]

Hi,
How I can implement my filter  for date 7 days from now in my filter like in example below.
I've created Last7Days measure but can not move into the filter box, tried all types of filtering.

Thanks

M

[Shahid12523 · Accepted solution]

You can’t put a measure in the filter pane.

Use one of these:

Relative date filtering on your Modified On column → set to last 7 days.

Or create a column:

IsLast7Days =
IF('Table'[Modified On] >= TODAY()-7 && 'Table'[Modified On] <= TODAY(), 1, 0)

Then filter IsLast7Days = 1.

[dainova]

Thanks Shahed !!!
I realized I made an error in my post, need rows where date older then 7 days from now, but I got an idea now, I know how measure works, strange it can not use it in the filter box.

But I need to solve another problem on the way, I can not add new column, in data view I have this message, I work with Dataverse source, need to check what is going on.

Best
D

[Ritaf1983]

Hi @dainova 
You can create flag column in the calculated table, with the formula like:

Last 7 days flag = if('Calendar'[Date]>= TODAY()-7 && 'Calendar'[Date]< TODAY(),1,0)
This column can be used as a filter in all the levels ( visual / page/ all pages)

The pbix with the example is attached

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

[dainova]

Thanks so much Rita !

[KarinSzilagyi]

Please correct me if I'm wrong, but I was taught to avoid calculated columns, especially for simple cases like this where it would only affect a few entries, since they bloat the model.

They're also only refreshed on load, so if the Semantic Model isn't refreshed daily, this would give wrong results since the evaluated today() would always point to the last refresh date. 

Due to those points I personally would use a filter-measure with the same logic instead, but please feel free to correct me if I'm wrong.

[Mauro89]

Hi @dainova,

if I understand your request correctly, there is a perfect match for this in PBI. 
In the screenshot you shared from the drop down there is the option "Relative date". Select this, then choose your granularity (in your case days) and type in 7. Additionally you can choose if you want to include the current day or not. 

I hope this fits your purpose?

If yes feel free to mark the post as solution. 

Best regards!

[dainova]

Thanks Mauro and all,

Sorry I realized I made an error in my question,  I need rows where dates older then  7 days from now, but  I got how to do it now.  

Relative filter doesn't have optiont NOT, which I need, so  I can not use, I need dates which are  NOT in the last 7 days. I learned that this feature is requested from MS but not implemented yet.
So I think I need go with extra calculated column,

Thanks to all for big help.