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

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 . 

Super User
Super User

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 

Super User
Super User

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

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