How to check to see if date in a table exists with in a range of dates in another table (Tables are not linked as they do not have unique field to link)

Power BI
1

Hello!

I have a table with Dates (table1) which we are putting the days throughout the year that our shop will be closed. We are adding to this table as we find out when the shop will be closed so it will be changing continuously.

I would like to compare this field in table1 to see if its greater than a start date on table2 and less than an end date on table2 (table2 is our ERP scheduling table). If its between this range I want to display a notice that the shop is closed. The other kicker is that the tables are not linked because table1 only has a few days in it.

Assuming this will be some kind of If then statement but not sure how to compare the data from one table to another when they are not linked.

Any help is appreciated!
Preston

2

@Preston,

You’ll always get a better, more specific response when you provide a PBIX file on which we can build and test the requested solution. But in the absence of that here, I tried writing the following measure from scratch in DAX Cleanup. See how this works for you:

DATE Check =
VAR Tab2Min = 
CALCULATE(
    MIN( Table2 [Date] ),
    REMOVEFILTERS( Table2 [Date] )
) 

VAR Tab2Max = 
CALCULATE(
    MIN( Table2 [Date] ),
    REMOVEFILTERS( Table2 [Date] )
) 

VAR Result = 
IF(
    AND(
        SELECTEDVALUE( Table1 [Date] ) > Tab2Min,
        SELECTEDVALUE( Table1 [Date] < Tab2Max ),
        1,
        0
    ) 

RETURN
Result

I hope this is helpful.

– Brian

3

Thanks for posting your question @Preston. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question

4

Hi @Preston, we’ve noticed that no response has been received from you since the 17th of November. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!