Help with volume used for the day

[Human]

Hi, I want to create a calculated column where it could calculate the usage of the volume for the day. I was hoping that it could subtract the volume reading from the start of the day with the end of the day at a specific time (06:30:00 at the morning and 11:30:00 at night) and display the result in the calculated column.

The volume used per day is the column that I am aiming to create. Please help..

[some_bih · Accepted solution]

Hi @Human possible solution: create calculated column as request like below, adjust Sheet1 to your table name

Volume used per day =

VAR __current_time = Sheet1[timestamp ]

VAR __start_of_day =

    DATE ( YEAR ( Sheet1[timestamp ] ), MONTH ( Sheet1[timestamp ] ), DAY ( Sheet1[timestamp ] ) )

        + TIME ( 6, 30, 0 )

VAR __end_of_day =

    DATE ( YEAR ( Sheet1[timestamp ] ), MONTH ( Sheet1[timestamp ] ), DAY ( Sheet1[timestamp ] ) )

        + TIME ( 23, 30, 0 )

VAR __volume_start_day =

    CALCULATE (

        SUM ( Sheet1[Volume] ),

        FILTER ( ALL ( Sheet1 ), Sheet1[timestamp ] = __start_of_day )

    )

VAR __volume_end_day =

    CALCULATE (

        SUM ( Sheet1[Volume] ),

        FILTER ( ALL ( Sheet1 ), Sheet1[timestamp ] = __end_of_day )

    )

RETURN

    IF ( __current_time = __end_of_day, __volume_start_day - __volume_end_day, 0 )

Did I answer your question? Kudos appreciated / accept solution!

[Human]

Thank you very much @some_bih !!.. this is exactly the solution that I've been aiming..