Filter out transactions in last 20 hours

[Anonymous]

Hi everyone. I have a table contains all transaction that happened last 7 days in factory. It has a column contains "Date Time" type of data that accurate to seconds. How can I create a visual that only display those transactions which happened in last 20 hours?

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Sample for your reference.

1. Create a measure as below.

Measure = 
VAR _now =
    NOW ()
VAR _diff =
    DATEDIFF ( MAX ( 'Table1'[date] ), [now], HOUR )
RETURN
    IF ( _diff <= 20 && _diff >= 0, 1, 0 )

2. Make the visual filtered by the measure.

Pbix as attached.

Regards,

Frank

[jsh121988]

MyMeasure = 
CALCULATE(
    [SomeAggregate],
    FILTER(MyTable, [MyDateTime] >= MAX(MyTable[MyDateTime]) - (20/24))
)

MyMeasure = // ALL is used here to remove any filters from MAX(DateTime)
CALCULATE(
    [SomeAggregate],
    FILTER(MyTable, 
        [MyDateTime] >= CALCULATE( MAX(MyTable[MyDateTime]) - (20/24)), ALL(MyTable) )
)