Forum Discussion
Rolling count per hour
Need help.
I have the below table (Columns: "Task ID" and "Receipt"). I need to calculate the column "Rolling total per hour" whcih is number of tasks recieved in the previous hours (an example of what I am trying to achieve is listed below).
Please help in providing the DAX fomula to create the calculated column. I tried to develop one as well as search the community but couldn't fine.
Table: Task Log
| Task ID | Receipt | Rolling total tasks per hour <<Calculated column>> |
| E496961 | 12/1/19 12:00 AM | 1 |
| E496962 | 12/1/19 12:13 AM | 2 |
| E496963 | 12/1/19 12:14 AM | 3 |
| E496964 | 12/1/19 12:17 AM | 4 |
| E496965 | 12/1/19 12:20 AM | 5 |
| E496966 | 12/1/19 12:22 AM | 6 |
| E496967 | 12/1/19 12:28 AM | 7 |
| E496968 | 12/1/19 12:29 AM | 8 |
| E496981 | 12/1/19 1:26 AM | 3 |
| E496982 | 12/1/19 1:53 AM | 2 |
| E496983 | 12/1/19 1:53 AM | 3 |
| E496984 | 12/1/19 2:16 AM | 4 |
Hi,
This calculated column formula works
Column = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Receipt]>=EARLIER(Data[Receipt])-TIME(1,0,0)&&Data[Receipt]<=EARLIER(Data[Receipt])))Hope this helps.
5 Replies
- Ashish_Mathur
Super User
Hi,
After 8, why do the figures decline? Please explain.
Ashish_Mathur - It is counting the number of tasks recieved within the last hour only (including the current tasks), in this case number of tasks recieved between 12:26 AM and 1:26 AM (both time inclusive), hence 3
- Ashish_Mathur
Hi,
This calculated column formula works
Column = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Receipt]>=EARLIER(Data[Receipt])-TIME(1,0,0)&&Data[Receipt]<=EARLIER(Data[Receipt])))Hope this helps.
Ashish_Mathur - Thanks a lot Ashish, the solution works.
- Ashish_Mathur
You are welcome.