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

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

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

      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.