Show <= Value Date based on the date selected

[nirvana_moksh]

Hello All,

I have a date filter slicer set which is populated from one table and based on the selection made in the filter I want the measure to look up the start dates column of another table and pull the closest value date from there. So if the Date Filter has 05.10.2018 selected and the lookup table for a particular id contains two start dates 05.09.2018 and 05.14.2018 then the measure should return 05.09.2018, but if 05.16.2018 is selected in the filter it should display 05.14.2018. 

The measure that I have written so far ignores the date filter context and does a straight min or max of the lookup start dates column instead which is not how I intend it to work.

Any help will be greatly appreciated.

Thank You

[ChandeepChhabra]

@nirvana_moksh 

See if this is what you need.

You can download the Power BI file here

Thanks

[nirvana_moksh]

@Ashish_Mathur and @ChandeepChhabra , your solutions helped so much! thanks again for taking the time out, but for some reason when I replaced the dummy values with  the data coming from SQL, there are times when this measure works and times when it doesnt, do you think it might be a data type issue? Also, my date for my slider are set to MM/DD/YYYY, but weirdly when I make a date selection the selection shows up with a time stamp. Power BI bug maybe?

[Ashish_Mathur]

Hi,

Try this measure

=MAXX(FILTER(SUMMARIZE(VALUES(Table2[Start Date]),[Start Date],"ABCD",CALCULATE(MAX(Table2[Start Date]),ALL(Table2[Start Date])),"EFGH",MAX(Table1[Date])),[EFGH]>[ABCD]),[ABCD])

Table1 is the Table with the Date column that you have dragged to your slicer.

Hope this helps.

[nirvana_moksh]

@Ashish_Mathur - Sadly it did not work. I think in short what I want the measure to do is based on the selected date it looks up the closest (<=) Start Date for the selected group id. In my head, it so simple, but trying to do that via DAX is turning out to be painful this time around

Here is the PBIX file with sample data wherein I tried using it - https://1drv.ms/f/s!Au9CSosUldp9hE-ZyXFm9ppRHpF1

[Ashish_Mathur]

Hi,

You may download my solution PBI file from here.

Hope this helps.

[hthota]

Create a measeure as given below:

Measure= if(Date<=Selectedvalue(Date),TableName[Date],Blank())

[nirvana_moksh]

@hthota- that wont work again because A.) the true result has to have a aggregation for that date, B.) if the aggregation on the column of date is added it will do a plain min,max on the look up date table instead of doing it in context of the date filter