cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JDro
Helper I
Helper I

Filter with dynamic lookup on today()

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

View solution in original post

3 REPLIES 3
westwrightj
Resolver III
Resolver III

@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

 

westwrightj_0-1605197132311.png

 

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

 

Capture.JPG

 

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

 

Capture.JPG

 

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

 

 

Capture.JPG

amitchandak
Super User
Super User

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

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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Thank you! This worked perfectly.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors