Forum Discussion

steen_p's avatar
steen_p
Helper II
5 years ago
Solved

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

 

Need Help

4 Replies