calculate count for last 1 hour from latest datetime

[jitpbi]

Hi,

I need to display the count of fault types for last 1 hour from the maximum datetime from the below dataset:

i tried with the below dax but it is giving incorrect value:

In the below screenshot, the highlighted fault count should not be displayed if we refer the last 1 hour fault type from the table where MOGAlarm is the only fault type the why Healthy is coming:

Please suggest how to do it correctly.

Thanks

[Anonymous · Accepted solution]

Don't @jitpbi,

By my understanding, you want to count all the categories when their time comes an hour ago, right?

You can use the following formula:

Measure =
VAR _max =
    MAXX ( ALL ( 'Table' ), 'Table'[Time] )
RETURN
    CALCULATE (
        COUNT ( 'Table'[Cate] ),
        FILTER ( 'Table', DATEDIFF ( 'Table'[Time], _max, SECOND ) <= 3600 )
    )

The final output is shown below:

Please take a look at the pbix file here.

Best regards
Qin eyelids
If this post helps, consider ACCEPT as the solution to help other members find it faster.

[Shishir22]

Hello @jitpbi ,

I would suggest divide datetime into 2 calculated columns one with date and one with time.

Date = FORMAT(Fault[DateTime].[Date],"dd/mm/yyyy")

Time = FORMAT(Fault[DateTime],"hh:mm:ss")

Please give datatype as date and time respectively.

Create measure for count as :

FaultTypeCount = 
COUNTX(FILTER(Fault,Fault[Date]=MAX(Fault[Date]) && Fault[Time]>=(MAX(Fault[Time])-1/24)),Fault[Fault_type])

If it fulfils your requirement, please mark it as solution.

Thanks!