cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Sum between 07 and 07(Today's and Yesterday's)

Hey all,

Im trying to sum the values between Today's and yesterday's date between 07:00 and 07:00 (7 am to 7 am).

sample data:

 Datetime Value 10/6/2020 6:05 5610 10/6/2020 8:05 1103 10/6/2020 9:05 7313 10/6/2020 10:05 5961 10/6/2020 11:05 1798 10/6/2020 12:05 1849 10/6/2020 13:05 1231 10/6/2020 14:05 7773 10/6/2020 15:05 9199 10/6/2020 16:05 1439 10/6/2020 17:05 7551 10/6/2020 18:05 7546 10/6/2020 19:05 9752 10/6/2020 20:05 438 10/6/2020 21:05 3461 10/6/2020 22:05 7773 10/6/2020 23:05 761 11/6/2020 0:05 2809 11/6/2020 7:05 3666

Ariel

1 ACCEPTED SOLUTION
Community Champion

Hi @Anonymous

Try this measure

``````Measure =
VAR __end = TODAY() + TIME( 7, 0, 0 )
VAR __start = __end - 1
RETURN
CALCULATE(
SUM( 'Table'[Value] ),
KEEPFILTERS(
'Table'[Datetime] >= __start &&
'Table'[Datetime] < __end
)
)``````

the result based on your sample will be.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

4 REPLIES 4
Helper IV

Hello @Anonymous ,

I suggest you to add new Date column to your data table

``Date = FORMAT('Time Table'[DateTime], "mm/dd/yyyy")``

After that, you can create the following Total measure

``````Total =
VAR timeSlicer =
FORMAT ( TIME ( 7, 0, 0 ), "hh:mm:ss" ) //set your time here
VAR currentDay =
SELECTEDVALUE ( 'Time Table'[Date] ) + timeSlicer
VAR prevDay = currentDay - 1
VAR result =
CALCULATE (
SUM ( 'Time Table'[Value] ),
FILTER (
ALL ( 'Time Table' ),
'Time Table'[DateTime] < currentDay
&& 'Time Table'[DateTime] >= prevDay
)
)
RETURN
result``````

To see the result - drag the new Date column and Total measure into a table

Anonymous
Not applicable

I tried your solution but it didn't work - It didn't show me any values.

I copied your steps and still it didn't work 😞

I really appreciate your help anyways!

Ariel

Community Champion

Hi @Anonymous

Try this measure

``````Measure =
VAR __end = TODAY() + TIME( 7, 0, 0 )
VAR __start = __end - 1
RETURN
CALCULATE(
SUM( 'Table'[Value] ),
KEEPFILTERS(
'Table'[Datetime] >= __start &&
'Table'[Datetime] < __end
)
)``````

the result based on your sample will be.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Anonymous
Not applicable

Hey @Mariusz ,

Thank you very much 🙂

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors