Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 suppor...
Need Help
az38's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

     

    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.

     

    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.