Power BI - Calculate the count of rows by category in the last hour using date/time

Question

I'm trying to calculated the Top 3 categories of tasks using DAX, the dataset get updated every 15 minutes, is there any function in DAX can calculate the count of rows between a datetime and current time.

Please take a look to the my dataset, I have the table bellow called Tasks.

I have tried the DAX code bellow but it only calculate the TOP 3 on the current day and not by the last hour.

TOP_3 =
CALCULATE (
    COUNT ( Tasks[id_task] ),
    FILTER (
        Tasks,
         (
            DATEDIFF (
                Tasks[Start DateTime],
                TODAY (),
                HOUR
            ) <= 1
        )
    )
)

So need your guys help in cracking a solution. Thanks in advance.

Answer 1

The probleme is in the TODAY() function, wich returns the time value 12:00:00 PM for all dates whereas The NOW function is similar but returns the exact time.

So use Now() instead of TODAY().

TOP_3 =
CALCULATE (
    COUNT ( Tasks[id_task] ),
    FILTER (
        Tasks,
         (
            DATEDIFF (
                Tasks[Start DateTime],
                NOW (),
                HOUR
            ) <= 1
        )
    )
)