cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jmowen
Regular Visitor

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

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.

 

jmowen_0-1635195766566.png

 

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

jmowen_1-1635195766569.png

 

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.

 

 

4 REPLIES 4
TheoC
Memorable Member
Memorable Member

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.

TheoC_1-1635208686043.png

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 🙂

 

TheoC_0-1635208656981.png

 

Hope this helps 🙂

 

Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

jmowen
Regular Visitor

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
Memorable Member
Memorable Member

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

jmowen
Regular Visitor

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

 

jmowen_4-1635301769157.png

 

jmowen_0-1635301327698.png

 

 

jmowen_1-1635301327701.png

 

jmowen_2-1635301327705.png

 

jmowen_3-1635301327715.png

 

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.