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.
@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?
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.
@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
You may download my solution PBI file from here.
Hope this helps.
@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
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.