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

[Anonymous]

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

Thanks for your help,

Ariel

[Hennadii]

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]

Hey @Hennadii,

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

[Mariusz · Accepted solution]

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

[Anonymous]

Hey @Mariusz ,

Your solution worked!

Thank you very much 🙂