cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Thai_Nguyen96
Helper I
Helper I

Calculate last hour data

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.

TimeCount
01/01/2018 10:00:00 PM4
01/01/2018 11:00:00 PM2
02/01/2018 12:00:00 AM2
02/01/2018 01:00:00 AM6
02/01/2018 02:00:00 AM4
02/01/2018 03:00:00 AM9
02/01/2018 04:00:00 AM0

 

Is there any DAX function support calculate last hour?

 

1 ACCEPTED SOLUTION
Thai_Nguyen96
Helper I
Helper I

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/

View solution in original post

7 REPLIES 7
Thai_Nguyen96
Helper I
Helper I

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

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
Community Champion
Community Champion

@Thai_Nguyen96 

what is your desired result for this data sample?

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Hi @az38 ,

 

My desired result if last 2 hours is:

 

TimeCountcount last 2 hours
01/01/2018 10:00:00 PM4 
01/01/2018 11:00:00 PM2 
02/01/2018 12:00:00 AM24
02/01/2018 01:00:00 AM62
02/01/2018 02:00:00 AM42
02/01/2018 03:00:00 AM96
02/01/2018 04:00:00 AM04

 

@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))

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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.

 

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.

c1.png

 

Here is the measure.

 

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

 

 

Result:

c2.png

 

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.

 

TermDefinition
datesA column that contains dates.
number_of_intervalsAn integer that specifies the number of intervals to add to or subtract from the dates.
intervalThe 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.

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.