cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors