Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
steen_p
Helper II
Helper II

Get Value from Date table based on selected date

Hi,

 

I need a little input, on how to get a value from a table, based on another value. 

I'm working on showing the number of contacts created in a given week and then also get the number form the previous week. I'm loading data from our datawarehouse and in our date dimension we have a WeekID, that is simply an incrementing number for each week. 

What I then want to do, is to get the weekId for the week of the highest date in the date selection. That means, if the user have selected a specific date, then that date, if it's a week then the highest date in that week and the same for the month or year. And if no date have been selected, then just take the current date.

 

What I have done so far just to get the WeekID is this  -

 

SelectedWeekID = VAR _MaxSelectedDate = CALCULATE(MAX('Date'[Date]) )
RETURN
CALCULATE( VALUES ('Date'[WeekID]),
                FILTER( ALL ('Date'[Date]), 'Date'[Date] = _MaxSelectedDate))

 

My two questions is then -

 

1: How do I modify this, so I set my _MAxSelectedDate to TODAY is there are no selected date? I have tried some combinations with SELECTEDVALUE because here I can have a default value, but I can't get this to work together with MAX ?

 

2: Is the above code, the best way to get a value from a table based on another value? In my case, I just want to get a Date value and select the corresponding WeekID value from my Date dimension. Coming from a SQL world, the above code looks a little over complicated just to do such a simple thing so am I overcomplicating it?

 

Regards

Steen

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @steen_p ,

 

Try this:

Today = TODAY()
SelectedWeekID 2 =
IF (
    ISFILTERED ( 'Date'[Date] ),
    MAX ( 'Date'[WeekID] ),
    WEEKNUM ( [Today], 1 )
)

selectedweekid.gif

 

 

 

Best Regards,

Icey

 

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

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

Hi @steen_p ,

 

Try this:

Today = TODAY()
SelectedWeekID 2 =
IF (
    ISFILTERED ( 'Date'[Date] ),
    MAX ( 'Date'[WeekID] ),
    WEEKNUM ( [Today], 1 )
)

selectedweekid.gif

 

 

 

Best Regards,

Icey

 

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

amitchandak
Super User
Super User

Hi @amitchandak 

 

Sorry for the late reply, but thanks for you reply. It seems like you are doing something along the line of what I've ben thinking :-).

Do you also have an input, on how I can use todays date as "selection" for the date in case the user haven't selected anything?

 

Regards

Steen

@steen_p , for today you have to create flag in date table and filter it

Is Today = if('Date'[Date]=TODAY(),"Today",[Date]&"")

 

Refer: https://www.youtube.com/watch?v=hfn05preQYA

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors