Calculate last hour data

[Thai_Nguyen96]

Hi all,

I'm pretty new in Power BI, I'm seeking a solution calculate last specific hour.

As I searched on Google, Power BI has a DATEADD function can help this but this function just only support for calculate previous dates.

My data has Time field per hour and count data for each hour.

Time	Count
01/01/2018 10:00:00 PM	4
01/01/2018 11:00:00 PM	2
02/01/2018 12:00:00 AM	2
02/01/2018 01:00:00 AM	6
02/01/2018 02:00:00 AM	4
02/01/2018 03:00:00 AM	9
02/01/2018 04:00:00 AM	0

Is there any DAX function support calculate last hour?

[Thai_Nguyen96 · Accepted solution]

Hi all,

I found the solution and want to share with you.

Please following this link.:

https://exceleratorbi.com.au/solving-a-complex-time-problem-in-power-bi/

[v-alq-msft]

Hi, @Thai_Nguyen96 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[az38]

@Thai_Nguyen96 

what is your desired result for this data sample?

[Thai_Nguyen96]

Hi @az38 ,

My desired result if last 2 hours is:

Time	Count	count last 2 hours
01/01/2018 10:00:00 PM	4
01/01/2018 11:00:00 PM	2
02/01/2018 12:00:00 AM	2	4
02/01/2018 01:00:00 AM	6	2
02/01/2018 02:00:00 AM	4	2
02/01/2018 03:00:00 AM	9	6
02/01/2018 04:00:00 AM	0	4

[az38]

@Thai_Nguyen96 

it depends on how  you calculate count but try

Measure = CALCULATE(SUM([Count]), FILTER(ALL('Table'), DATEDIFF('Table'[Time], SELECTEDVALUE('Table'[Time]), HOUR)=2))

[Thai_Nguyen96]

Hello @Anonymous ,

I currently cannot get the desired result.

Let me clarify a little bit, It's similar like DATEADD but applied for HOUR.

I cannot use this function due to the Time field data has the duplicate date.

[v-alq-msft]

Hi, @Thai_Nguyen96 

As is suggested by @az38 , It will work when the Time field has duplicate dates. I created data to reproduce your scenario.

Here is the measure.

Count Last 2 hours = 
CALCULATE(
    SUM('Table'[Count]),
    FILTER(
        ALLSELECTED('Table'),
        DATEDIFF(
            'Table'[Time],
            SELECTEDVALUE('Table'[Time]),
            HOUR
        )=2
    )
)

Result:

Here is the document  about Dateadd function. 

DATEADD(<dates>,<number_of_intervals>,<interval>)  

It returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context.

Term	Definition
dates	A column that contains dates.
number_of_intervals	An integer that specifies the number of intervals to add to or subtract from the dates.
interval	The interval by which to shift the dates. The value for interval can be one of the following: year, quarter, month, day

There is no hour interval for the function. I don't think it is able to use Dateadd function in this scenario.

Best Regards

Allan

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