Power BI Iteration Sum based on Consecutive Hours

[nicolealexi]

Hello. I'm trying to write a new column dax to sum a values column based on a four hour consecutive window.  Every 4 hour window would have its own sum. Theoretically, there would be 24 hour rows for each resource on that specific day but I have a limited sample size. For those 24 hours, the date and resource would be the same. For example, on a certain day there would be 24 rows in the hour column for hours 1-24 where the date would read 1/1/2022 and the resource would read 1 for each hour. Resource 1 and 2 would have the same format and for the next day, 1/2/2022. Hours 1-3 would blank since they do not meet the 4 hour consecutive yet. Row 4 of the new column is the value sum of hours 1-4 and the 5th row in the new column is the value sum of hours 2-5. I need a dax formula that would create this sum up until hour 24 which can also be filtered by desired resource or date.

Resource	Date	Hour	Value	New Column
1	1/1/2022	1	72
1	1/1/2022	2	31
1	1/1/2022	3	0.3
1	1/1/2022	4	0	103.3
1	1/1/2022	5	0.2	31.5
2	1/1/2022	1	10
2	1/1/2022	2	11.2
2	1/1/2022	3	-9
2	1/1/2022	4	16	28.2

[Anonymous]

Hi  @nicolealexi ,

Here are the steps you can follow：

1. Create calculated column.

Falg =
var _count=
COUNTX(
    FILTER(ALL('Table'),'Table'[Resource]=EARLIER('Table'[Resource])),[Hour])
return
IF(
    _count<=3,BLANK(),
    IF(
       [Hour]>3,
        SUMX(
            FILTER(ALL('Table'),            'Table'[Resource]=EARLIER('Table'[Resource])&&'Table'[Hour]>=EARLIER('Table'[Hour])-3&&'Table'[Hour]<=EARLIER('Table'[Hour])),[Value])))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly