Find if specific date it's between range of dates

[hdzhendov]

Hello Everyone,
This is my first post in the Power BI community.
I spend more than a week struggling with this issue, reading posts, and trying new approaches, but without success.

I have a table (screenshot of the semple is below) that hold the data for the state of all the location.
So, I have FromDate - this is the date when the change occurred, and to date - this is the date when another event occurred that ended this state, and a new one starts.
I have LocationID that's unique for each location and StatusID that indicate the status of the location (let's say the location is open or closed).
I'm ending with many rows for each location with the period from-to, and status for this period.

What I'm trying to do is build a report that returns the status (actually I'm counting location) of those locations for a specific date.

Example:
I run the report for 03/25/2019, and I want to see how many locations were open on that date. 
This is how I'm trying to determinate this:
I'm adding a new calculated column to the table:

isLocationActive = 
var fromDate    =   FORMAT(LocationHistory[FromDate].[Date], "mm/dd/yyyy")
var ToDate      =   FORMAT(LocationHistory[ToDate].[Date], "mm/dd/yyyy")

return
IF(
    AND(
        fromDate    <=  [Selected Date],
        ToDate      >=  [Selected Date]
    ),
        "yes",
        "no"
)

In this current example [Selected Date] is measure:

Selected Date = 
VAR selectedDate  = FORMAT( CALCULATE( MAX( 'Calendar'[Date] ), ALLSELECTED('Calendar'[Date] )), "mm/dd/yyyy")

return
selectedDate

Which I found out is not working and can't be a measure but I'm not actually sure why. If I put the measure in the report and change the date with slider, the date of the measure is correct, but in the calculated column I receive for start date 2016 and end date 2017 that report date in 2019 is between those two dates.
I tried few different approches and none of them worked.

Thank you for any help that you may provide on this case.
Best regards,
Hristo

[v-frfei-msft]

Hi @hdzhendov ,

The measure is dynamic but the calculated columns are computed during the database processing and then stored in the model. So In your scenario, we cannot make the calculated column dynamically.

We can create a measure to return the statues of those locations to work around.

isLocationActive = 
VAR selectedDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    IF (
        AND (
            SelectedDate >= SELECTEDVALUE ( LocationHistory[FromDate] ),
            SelectedDate <= SELECTEDVALUE ( LocationHistory[ToDate] )
        ),
        "yes",
        "no"
    )

[hdzhendov]

Hello @v-frfei-msft ,
Unfortunately, this is not returning the result I'm expecting.

I've tried many different ways with measures and I always go around this result. Where Power BI compare dates between let's say 2014 to 2016 with 2019 and it says that 2019 it's in that range.
Check the screenshot below.

Best regards,

[hdzhendov]

Other ideas?

[v-frfei-msft]

Hi @hdzhendov ,

Kindly share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.