cancel
Showing results for
Did you mean:
Regular Visitor

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

I am looking for logic to determine how to pull the following data

I have a fact table that has transaction date and time and computer per transaction the second table is the device table showing who was logged into the device (there is no log out). My intended logic will be to link date, and device ID and then find the time that is the first time found less the log in time from the fact table . So the first entry I want to determine BOB was logged and second ROB

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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super Contributor

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

This seems to have been deemed an M question, but here is one of those "dime a dozen" DAX solutions:

PBIX sample

Calculated column in Transactions:

```Person =
VAR TransactionDateTime = Transactions[DateTime]
RETURN
CALCULATE (
//Arbitrary tie-breaker
CALCULATETABLE (
)
)```

Owen

Proud to be a Datanaut!

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!

15 REPLIES 15
Super Contributor

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

Maybe DAX would be more appropriate, but in M (Edit Queries) it can be done with the following code.

I combined dates and times in each table to the field "DateTime".

Steps:

• Merge table Logins with Transactions (Logins are nested tables in Transctions table)
• Expand the nested table, returning only the name
• Apply type text to the Name

```let
LatestLogin = Table.TransformColumns(RemovedLogins, {{"Logins before Transaction", each Table.SelectRows(_, let latest = List.Max(_[DateTime]) in each _[DateTime] = latest)}}),
in
Typed```

By the way, the second is SAM, not ROB.

Specializing in Power Query Formula Language (M)
Super Contributor

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

Folks it seems @MarcelBeug maybe giving our undisputed M expert @ImkeF a run for her money!

Super Contributor

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

Thanks @Sean, @ImkeF is aware.

Specializing in Power Query Formula Language (M)
Super Contributor

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

Super Contributor

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

@Sean Especially with Power Query I think that videos have enormous added value, as code is typically generated by clicking the right buttons on the menus.

A good example in https://community.powerbi.com/t5/Desktop/Parsing-Embedded-JSON/m-p/118765#M50133 in which the solution is basically simple and straightforward, but it requires a lot of clicks.

Even with a video it takes 3 minutes to explain.

Specializing in Power Query Formula Language (M)
Super User

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

Happy to hand-over :-)

Proud to be a Datanaut!

Imke Feldmann

Super Contributor

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

@ImkeFif we can poll this community one thing will be certain!

When it comes to DAX there are many so called "experts"

But when it comes to M everyone will vote for you!

So don't hand over anything yet!

Super Contributor

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

When it comes to theoretical M knowledge, I think @ImkeF can more or less compete by now.

With regard to practical experience I'm definitely lagging behind.

But I have that screencasting software as a card in my sleeve.

And my userid is with a capital M...

Specializing in Power Query Formula Language (M)
Super Contributor

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

This seems to have been deemed an M question, but here is one of those "dime a dozen" DAX solutions:

PBIX sample

Calculated column in Transactions:

```Person =
VAR TransactionDateTime = Transactions[DateTime]
RETURN
CALCULATE (
//Arbitrary tie-breaker
CALCULATETABLE (
)
)```

Owen

Proud to be a Datanaut!

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 336 members 3,264 guests
Recent signins: