Forum Discussion
Difference between current and previous cell, reset every new day
Hello,
I have this kind of data set:
| Date | Time | Accumulative count | Amount per hour |
| 01/05/2022 | 01:00 | 1 | 1 |
| 01/05/2022 | 02:00 | 2 | 1 |
| 01/05/2022 | 03:00 | 7 | 5 |
| 01/05/2022 | 04:00 | 9 | 2 |
| 01/05/2022 | 05:00 | 15 | 6 |
| 02/05/2022 | 01:00 | 3 | 3 |
| 02/05/2022 | 02:00 | 9 | 6 |
| 02/05/2022 | 03:00 | 12 | 3 |
| 02/05/2022 | 04:00 | 25 | 13 |
| 02/05/2022 | 05:00 | 28 | 3 |
| 03/05/2022 | 01:00 | 0 | 0 |
| 03/05/2022 | 02:00 | 15 | 15 |
| 03/05/2022 | 03:00 | 18 | 3 |
| 03/05/2022 | 04:00 | 19 | 1 |
| 03/05/2022 | 05:00 | 23 | 4 |
So basicly I have an accumulative count, that gets reset every day at a certain time. What I want to know is the amount per hour, but reset every day. I tried with a merged double index, one starting at 0, the other at 1, but then I dont take the dates into account.
Anyone has an idea how to fix this in power query?
Many thanks!
Nico
Nico_Beckers , Try a new column like
New column =
var _date = datevalue([Date Time])
var _max = maxx(filter(Table, [Date Time] < earlier([Date Time]) && datevalue([Date Time]) = _date), [Date Time] )
return
[Accumulative] - maxx(filter(Table, [Date Time] =_max && datevalue([Date Time]) = _date), [Accumulative] )
4 Replies
- amitchandak
Super User
Nico_Beckers , Try a new column like
New column =
var _date = datevalue([Date Time])
var _max = maxx(filter(Table, [Date Time] < earlier([Date Time]) && datevalue([Date Time]) = _date), [Date Time] )
return
[Accumulative] - maxx(filter(Table, [Date Time] =_max && datevalue([Date Time]) = _date), [Accumulative] ) - Nathaniel_C
Community Champion
Hi Nico_Beckers ,
Please try this:Dax Accumulative Count = VAR _date = MAX ( dif[Date] ) VAR _Time = MAX ( dif[Time] ) VAR _amountPerHour = MAX ( dif[Amount per hour] ) VAR _calc = CALCULATE ( SUM ( dif[Amount per hour] ), FILTER ( ALL ( dif ), dif[Date] = _date && dif[Time] <= _Time ) ) RETURN _calc
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
NathanielHi Nathaniel,
This gives me the accumulative count no? I want to calculate the amount per hour starting from the accumulative count. Or did I miss something?
- Nathaniel_C
Hi Nico_Beckers ,
Sorry for misunderstanding. Still not sure what you want. Perhaps you could provide a picture of your expected outcome.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
