Filter with dynamic lookup on today()

[JDro]

Hi all,

I'm looking to do a page level filter to only show values for my time period lets call it "Winter", I have linked a dimensions table with every date in the year and what period they fall into, so I would like to be able to say if we're currently in the winter period then show me all values filtered by winter, and if today() equals summer then show all values on summer.

I created a lookup formula within a measure where CurrentPeriod = LOOKUPVALUE(DimDate[Period],DimDate[Date],Today()) but I cant drag that over to the page level filters and when dragged over to the visual level filters, I'm not sure how to set the filter to be dynamic?

Thanks

[westwrightj]

@JDro 

I think I might know what you are trying to do here. I don't have an elegant solution but this would ultiamtely work to meet the need I think

So I've made some sample data to start with for a dimension table

One thing you could do is first make a column to identify what season today falls into

Today Column = 

var TheDay_Today = TODAY()

return

IF(DimTable[Date] = TheDay_Today, DimTable[Season], "")

Which will give you something like this

Next you can make a column to only fill in seasons for when the Season matches the Today Column

Current Season = 

var TheMaxSeason_Today = CALCULATE(MAX(DimTable[Today Column]), ALL(DimTable))

return

IF(DimTable[Season] = TheMaxSeason_Today, 1, 0)

That will give you a binary column you can then use to reference as page level filter or within measures

That would allow the page level filtering to work and update as you shift through seasons

[amitchandak · Accepted solution]

@JDro , Create a new column like this in your table and select current

if([period] = maxx(filter(Table,[Date]=today()),[period]), "Current", [period])

[JDro]

Thank you! This worked perfectly.