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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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