Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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, @Anonymous 

 

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

@Anonymous 

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
Anonymous
Not applicable

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

 

az38
Community Champion
Community Champion

@Anonymous 

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
Anonymous
Not applicable

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, @Anonymous 

 

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.