cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nirvana_moksh
Impactful Individual
Impactful Individual

Show <= Value Date based on the date selected

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

7 REPLIES 7
ChandeepChhabra
Solution Specialist
Solution Specialist

@nirvana_moksh 

See if this is what you need.

Capture.PNG

You can download the Power BI file here

 

Thanks

@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?

 

DATE.PNG

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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

Hi,

You may download my solution PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
hthota
Resolver III
Resolver III

Create a measeure as given below:

 

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

nirvana_moksh
Impactful Individual
Impactful Individual

@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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.