Forum Discussion

Dunner2020's avatar
Dunner2020
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

24 hrs sum column

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.

 

Need Help
Show and Tell
Tips and Tricks
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Dunner2020
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

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

       

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Dunner2020
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      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's avatar
        v-deddai1-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        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