Floating window 24 hrs sum column in table

DAX DAX Calculations
1

Hi there,

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

image
image749×553 35.1 KB

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? The sample file can be download from here.

2

Hi @leo_89,

Thanks for posting this question. I believe what you are trying to achieve is to add [30 mins Value] for the next 47 occurrences. If you add index column using Power Query and then use below formula, you can get your desired result.

new column =
SUMX (
    FILTER (
        'Fact Table',
        'Fact Table'[Index] >= EARLIER ( 'Fact Table'[Index] )
            && 'Fact Table'[Index]
                <= EARLIER ( 'Fact Table'[Index] ) + 47
    ),
    'Fact Table'[30 Mins Value]
)

image

Solution file is attached.

Kind Regards,
Hafiz
sample9.pbix (84.6 KB)

1 Like
3

@hafizsultan, thanks for the reply. However, this is not what I am looking for. I dont think so that we need to add new column (index). EpochHH column is acting like a index column. As I mentioned in my post that EpochHH is the unique representation of half hourly time slot in a given day. By introducing index column, it does not add up 30 mins value of 24 hrs.

4

@hafizsultan, I replaced the index with EpochHH and it worked the way I want. If you could make a change in your formula then I would accept it as a solution. Thanks.

5

Hi @leo_89,

Happy that it worked for you by small adjustment. Cheers!

Kind Regards,
Hafiz

6

Hi @leo_89, did the response provided by @hafizsultan help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!