Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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/
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/
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
@Anonymous
what is your desired result for this data sample?
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 |
@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))
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |