CODEWITHSUNDEEP
datetimereferencepowerbidax
M0rrow
M0rrow

Reputation: 3

Is there a way of looking up a datetime in a datetime reference table and returning corresponding data

Have been searching for the answer to this for a which but with no joy. Hoping you DAX geniuses can help out!

I have a table of transactional data with a date time column (in the format "dd/mm/yyyy hh:mm:ss")

I want to look this datetime up in a separate 'shift reference' table to add a new column to my transactional data i.e. if it falls between 2 date times (which it always will), Start time and End Time there will be a corresponding shift associated with it.

The format of this table is Start time - End Time - Shift Pattern

In this table we have the datetime (in the same format as before) the shift started - "Start_Time", when it ended - "End_Time" and what 'Shift' was working. I want to use my transactional datetime to look up what shift was on when the transaction took place.

Ive tried combinations of Lookupvalue/Calculate/Max and on some occasions it has returned values, but never correct ones!

I hope this makes sense!

Best Regards, Colin

Upvotes: 0

Views: 109

Answers (1)

Kosuke Sakai
Kosuke Sakai

Reputation: 2411

You can use this code to add a calculated column with the Shift value looked up based on transaction timestamp.

Shift = CALCULATE (
    VALUES ( Shift[SHIFT_PATTERN] ),
    FILTER (
        Shift,
        Shift[start_time] <= [Timestamp] && Shift[end_time] > [Timestamp]
    )
)

Calculated Column

Just for showing another option, it is even possible to calculate transaction facts sliced by Shift without adding a column in the fact table. You may handle it in measures like below.

Transaction Count =
SUMX (
    Shift, 
    COUNTROWS (
        FILTER (
            'Transaction',
            'Transaction'[Timestamp] >= Shift[start_time]
                && 'Transaction'[Timestamp] < Shift[end_time]
        )
    )
)

Result

Upvotes: 0

Related Questions