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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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.

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors