cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sean Super Contributor
Super Contributor

Re: Find entry whose date time is less than but closest to fact table

The other thing this community will agree on @OwenAuger's solutions definitely do not fall in the "dime a dozen" category!

 

BTW Owen's sample file above includes Marcel's solution as well Smiley LOL @MarcelBeug

 

@brett_waltonyou can create the Device table in Owen's solution in the Query Editor

Duplicate the table that has ALL Devices (right-click the table)

Rename It

Remove All Other Columns

Remove Duplicates (right-click the column)

Close and Apply

 

Good Luck! Smiley Happy

brett_walton Regular Visitor
Regular Visitor

Re: Find entry whose date time is less than but closest to fact table

Thank you .. Will give it a try but for point of clarity of what I am looking for the second one is Rob . The transaction was at 10:13 and he logged into the device at 10:00 so it was he who posted it not SAM . 

Sean Super Contributor
Super Contributor

Re: Find entry whose date time is less than but closest to fact table


@brett_walton wrote:




                     Date      Time        Device

Example :      1/1/17   08:10:53   RFGN3550            

                     1/1/17   10:13:40   RFGN3560

 

I have a device Log table that says who was logged into that device 

                 Log in Date    Time       Device          Person

Example:           1/1/17   08:00:00   RFGN3550     BOB 

                         1/1/17  09:00:00    RFGN3550     MARY  

                         1/1/17  09:00:00    RFGN3560     SAM

                         1/1/17  10:00:00    RFGN3550    ROB


 

Sam is the only one logged in device 3560!

 

Maybe you meant to have Rob log in device 3560 - but as you've written it the second should be Sam

brett_walton Regular Visitor
Regular Visitor

Re: Find entry whose date time is less than but closest to fact table

Thank you Owen, that worked well.  I perferred the DAX solution over M as I actaully understand what you did. Smiley Very Happy  (work for a smaller company so I am not a full time data person) . 

 

One other huddle I need to cross 

 

If I have a transaction at 1/27/17 @ 00:10:00 AM AND the log in record am looking for is 1/26/17  22:40:00 (so they logged in the previous day) - is there another condition I can add to this to account for that ? 

 

Thank You Again 

Brett 

Highlighted
Sean Super Contributor
Super Contributor

Re: Find entry whose date time is less than but closest to fact table

@brett_walton

I suspect you've modified @OwenAuger's formula to only look at your Time columns!

 

Note that he checks a Date/Time column.

 

So to address this create a Date-Time Column in both tables like this

Date-Time = [Date]&" "&[Time]

Both of these columns will default to Text - so you need to convert them to Data Type - Date/Time - again in both tables.

 

This should resolve it!

 

Good Luck! Smiley Happy

View solution in original post

brett_walton Regular Visitor
Regular Visitor

Re: Find entry whose date time is less than but closest to fact table

Thank you , I noticed the merge I just didnt do 2 things 1) change my data type 2) open my device log fact tbale for mutiple days 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 53 members 1,104 guests
Please welcome our newest community members: