Get Value from Date table based on selected date

[steen_p]

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

[Icey · Accepted solution]

Hi @steen_p ,

Try this:

Today = TODAY()

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

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]

@steen_p , refer if my blogs can help

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[steen_p]

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

[amitchandak]

@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