Filter table based on start and End dates & times of the ID of that row.

[jmowen]

Hi I am new to power bi and have gotten a bit stuck.

My problem is I want to check if a value is greater than a value between a date range

(Start and end date & time).

I have one table that holds the value and the timestamp for when the value was logged.

I have a second table with the Start and end date & time I want to check between

I want to be able to filter the page by the ID column and return a table that Starts at the corresponding start date & time and ends at the corresponding end date & time.

e.g. if I filter the page by “ID 1” the table will only have the timestamps from 14/10/2021 6:00:00 am to 17/10/2021 6:00:00 am

then I can use conditional formatting on the “Value (m^3/s)” to display if a value within that data set was greater than a certain value.

[TheoC]

Hi @jmowen 

Can you create a Calculated Column and use the following:

CalCol = 

VAR _DateCheck = 'tblDateTime'[Value] 

RETURN 

MAXX ( FILTER ( 'tblID' , 'tblID'[StartDate] <= _DateCheck && _DateCheck <= 'tblID'[EndDate] ) , 'tblID'[ID] ) 

Once you've established this, you can then create a relationship (1:*) from the tblID to the tblFact and use the tblID[EmployeeID] as the Slicer for filter.

Output will be as per below screenshot.  My output has blanks because clearly I didn't fill out all of the TimeStamp data correctly in the sample data I put together lol 🙂

Hope this helps 🙂

Theo

[jmowen]

Hi @TheoC

Thanks for your reply 

I ran into one issue, I can't have Start Dates overlap with End Dates as the start dates will overrider the Ends Dates.

e.g. with the following 2 IDs ID 1, CalCol will only go out untill the start Date of ID 2.  

ID 1, Start Date 27-10-2021 10:00 AM, End Date 30-10-2021 10:00 AM

ID 2, Start Date 28-10-2021 10:00 AM, End Date 31-10-2021 10:00 AM

Please let me know if you have a solution for this problem, if not I am happy to accept the original solution as I did not communicate date would overlap in my original post. 

Jared

[TheoC]

@jmowen I ran into that on the first effort for a solution so authored the solution I presented which basically ignores any overlaps and returns based on the Employee ID.

In saying that, if you adjust the Start Date and End Date to whatever you want in your tblID, the outputs will automatically update and you do not need to change anything in the solution I have put forward 🙂

So basically adjust the Start and End Dates for ID 1 and ID 2 in the tblID and you're good to go 🙂

[jmowen]

Sorry maybe I’ve done something wrong, or I just don’t understand.

As my CalCol overlaps, ID 6 should go out unit the End date of 23/10/2021 1:00:00 pm, but because the ID 7 has a start date at 21/10/2021 8:00:00 am, ID 6 becomes cut off.  

Even if I use the slicer

Below is what i've done. I have 2 tables and created a Calculated Column as you advised