cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
zhangsiyan12134 Regular Visitor
Regular Visitor

Filter out transactions in last 20 hours

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter out transactions in last 20 hours

Hi @zhangsiyan12134 ,

 

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.

Capture.PNG

 

Pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
jsh121988 Regular Visitor
Regular Visitor

Re: Filter out transactions in last 20 hours

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) )
)
Community Support Team
Community Support Team

Re: Filter out transactions in last 20 hours

Hi @zhangsiyan12134 ,

 

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.

Capture.PNG

 

Pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.