24 hrs sum column

[Dunner2020]

Hi there,

I have a data set that looks like as shown in the picture:

In the data EpochHH is the unique number that represents the half-hour time slot of the given date. Now I want to create a new column that calculates the sum of [30 mins value] column in the next 24 hours. The 24-hour window should be floating. So I tried to create a formula that looks like as follow:

24 hrs Value = 

CALCULATE(SUMX(FILTER('Fact Table','Fact Table'[EpochHH] >= 'Fact Table'[EpochHH] && 'Fact Table'[EpochHH] <= 'Fact Table'[EpochHH] + 47),'Fact Table'[30 Mins Value]))

However, the above formula did not produce the desired result. Could anyone help me where I made the mistake? Sample file can be download from here.

[Jihwan_Kim]

Hi, @Dunner2020 

Please try the below.

24 hrs Value =
VAR currentHH = 'Fact Table'[EpochHH]
RETURN
SUMX (
FILTER (
'Fact Table',
'Fact Table'[EpochHH] >= currentHH
&& 'Fact Table'[EpochHH] <= currentHH + 47
),
'Fact Table'[30 Mins Value]
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Dunner2020]

@Jihwan_Kim , thanks for your reply. However, the above-mentioned formula does not produce the desired result as shown in the picture:

The first value shown in 24hrs values column should have been the sum of first two values of 30 Mins values column.

[v-deddai1-msft · Accepted solution]

Hi @Dunner2020 ,

Please use the following calculated column:

24 hrs Value = CALCULATE(SUM('Fact Table'[30 Mins Value]),FILTER('Fact Table','Fact Table'[EpochHH]<=EARLIER('Fact Table'[EpochHH])+47 &&'Fact Table'[EpochHH]>=EARLIER('Fact Table'[EpochHH])))

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

Best Regards,

Dedmon Dai

[Jihwan_Kim]

Hi, @Dunner2020 

I think you did not use mine. Sorry that I do not know why Calculate is in front of your formula.

Thank you.

[amitchandak]

@Dunner2020 , Try a new column like

24 hrs Value =
CALCULATE(SUMX(FILTER('Fact Table','Fact Table'[EpochHH] >= earlier('Fact Table'[EpochHH]) && 'Fact Table'[EpochHH] <= earlier('Fact Table'[EpochHH])+ 47),'Fact Table'[30 Mins Value]))

[Dunner2020]

@amitchandak , When I used earlier function it throws an error message that parameter is not correct type as shown in the following picture: