Can I use a Measure in Filter?

[Velocity]

Hi,

In my data table, i have many records with future dates. I would like to restrict my slicer to show dates up to last week's beginning date. I have created a measure

cutoff date = today() - (weekday(today(),2) - 6)

Now, i want to show only those dates in filter where transaction date is less that cutoff date but it seems you can't use a measure in visual or page or all pages filter. Any suggestions?

Thanks.

[amitchandak]

@Velocity ,Visual level filter should allow measure. Slicer and page-level will not

[Velocity]

@amitchandak

I guessed as much. Hence, i had created a calculated column to achieve what i was trying.

Thanks.

[v-lili6-msft]

hi  @Velocity 

First, you should know that:

1. Calculation column not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

So you could not use a measure as a slicer.

"I guessed as much. Hence, i had created a calculated column to achieve what i was trying."  It's pleasant that your problem has been solved, 😁 please accept the reply as solution, that way, other community members will easily find the solution when they get same issue.

Regards,

Lin

[rajulshah · Accepted solution]

Hello @Velocitym,

You can use a measure as 'Visual Level Filter'. However you can create a calculated column in the date table that returns zero for the days that you don't want to count in the measure.

On the basis of what I think your requirement is, I have created a calculated column as below:

Last Week StartDay = 
VAR CurrentWeek =  WEEKNUM(TODAY())
VAR LastWeek = CurrentWeek-1
VAR LastWeekBeginningDate = CALCULATE(FIRSTDATE('Date'[Date]),FILTER('Date',WEEKNUM('Date'[Date])=LastWeek))
RETURN IF(WEEKNUM('Date'[Date])>=LastWeek && 'Date'[Date]>LastWeekBeginningDate,1,0)

And you can use this colujmn to filter out the dates, as LastWeekStartDay<>1.

You can modify the start of the day as you want. I have taken the default start of the week.
Hope this helps. Please let me know if this doesn't work.

[Velocity]

Hello @rajulshah

I had already worked out Calculated Column solution but was hoping for 'Measure' solution.

Thanks for your input.