Difference between current and previous cell, reset every new day

[Nico_Beckers]

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

[Nathaniel_C]

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.
Nathaniel

[Nico_Beckers]

Hi 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

[amitchandak · Accepted solution]

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