Volume Used per day when there are refill in the day

[Human]

Hi all, 

I want to create a calculated column where it could calculate the usage of the volume for the day. I have a set of data where there are a few days where the volume was being used normally and also one day where there are a refill on the volume.

I was hoping that for the normal day, the volume used would be (volume at the start of the day would - the volume at the end of the day) and for the day that has a refill, it could:

1. Subtract the volume reading from the start of the day with the lowest volume reading of the day (before refill).
2. Subtracting the Highest volume reading of the day (after refill) with the volume reading at the end of the day.

Eventually, for the day that have a refill, both subtracted value from no1 and no 2 will be added together and the result would be reflected in the calculated column. 

timestamp	Volume	Volume Used
14/8/2023 6:01	1030	0
14/8/2023 12:27	1029	0
14/8/2023 23:59	1030	0
15/8/2023 0:00	1030	0
15/8/2023 0:01	1030	0
15/8/2023 7:23	994	0
15/8/2023 11:28	919	0
15/8/2023 14:11	884	146
16/8/2023 0:00	848	0
16/8/2023 7:48	812	0
16/8/2023 10:38	773	0
16/8/2023 10:39	739	0
16/8/2023 17:48	703	0
16/8/2023 23:59	668	180
17/8/2023 0:00	668	0
17/8/2023 11:31	595	0
17/8/2023 22:50	522	146
18/8/2023 0:00	522	0
18/8/2023 8:35	486	0
18/8/2023 12:02	449	0
18/8/2023 14:04	414	0
18/8/2023 14:05	594	0
18/8/2023 14:06	956	0
18/8/2023 14:07	1291	0
18/8/2023 14:08	1643	0
18/8/2023 14:09	1680	0
18/8/2023 16:48	1680	0
18/8/2023 16:49	1680	0
18/8/2023 16:50	1644	0
18/8/2023 23:50	1645	143

The above was what I have been trying to achieve. Any help is very much appreciated. Thank you in advance.

[v-yiruan-msft]

Hi @Human ,

I created a sample pbix file(see the attachment), please chekc if that is what you want. You can create two calculated columns as below to get it:

Date = DATEVALUE('Table'[timestamp])

Volume Used = 
VAR _mintm =
    CALCULATE (
        MIN ( 'Table'[timestamp] ),
        FILTER ( 'Table', 'Table'[Date] = EARLIER ( 'Table'[Date] ) )
    )
VAR _maxtm =
    CALCULATE (
        MAX ( 'Table'[timestamp] ),
        FILTER ( 'Table', 'Table'[Date] = EARLIER ( 'Table'[Date] ) )
    )
VAR _minvalue =
    CALCULATE (
        MAX ( 'Table'[Volume] ),
        FILTER ( 'Table', 'Table'[timestamp] = _mintm )
    )
VAR _maxvalue =
    CALCULATE (
        MAX ( 'Table'[Volume] ),
        FILTER ( 'Table', 'Table'[timestamp] = _maxtm )
    )
RETURN
    IF ( 'Table'[timestamp] = _maxtm, _minvalue - _maxvalue, 0 )

I have one doubt here: for the timestamp 18/8/2023 23:50, why the volume used is 143?

Best Regards

[Human]

@v-yiruan-msft So on 18/8/2023 23:50, it has a refill. I understand that the original formula (volume at the start of the day would - the volume at the end of the day) cannot be used so I got 143 by:

 [(volume at day start - Lowest volume of the day) + (Highest Volume of the day - volume at day end)]

[v-yiruan-msft · Accepted solution]

Hi @Human ,

Thanks for your feedback. I updated the sample pbix file(see the attachment), please find the details in it. Please update the formula of calculated column [Volume Used] as below:

Volume Used = 
VAR _mintm =
    CALCULATE (
        MIN ( 'Table'[timestamp] ),
        FILTER ( 'Table', 'Table'[Date] = EARLIER ( 'Table'[Date] ) )
    )
VAR _maxtm =
    CALCULATE (
        MAX ( 'Table'[timestamp] ),
        FILTER ( 'Table', 'Table'[Date] = EARLIER ( 'Table'[Date] ) )
    )
VAR _mintmvalue =
    CALCULATE (
        MAX ( 'Table'[Volume] ),
        FILTER ( 'Table', 'Table'[timestamp] = _mintm )
    )
VAR _maxtmvalue =
    CALCULATE (
        MAX ( 'Table'[Volume] ),
        FILTER ( 'Table', 'Table'[timestamp] = _maxtm )
    )
VAR _minvalue =
    CALCULATE (
        MIN ( 'Table'[Volume] ),
        FILTER ( 'Table', 'Table'[Date] = EARLIER ( 'Table'[Date] ) )
    )
VAR _maxvalue =
    CALCULATE (
        MAX ( 'Table'[Volume] ),
        FILTER ( 'Table', 'Table'[Date] = EARLIER ( 'Table'[Date] ) )
    )
VAR _diff1 = _mintmvalue - _maxtmvalue
RETURN
    IF (
        'Table'[timestamp] = _maxtm,
        IF (
            ABS ( _diff1 ) <= 400,
            _diff1,
            ( _mintmvalue - _minvalue ) + ( _maxvalue - _maxtmvalue )
        ),
        0
    )

Best Regards

[lbendlin]

Might be easier to use a graphical solution.

When is a refill a refill and not just a data fluke?  Is any positive change considered to be a refill? For example on 8/14 you go from 1030 to 1029 to 1030. is that a refill?

[Human]

@lbendlin For the change to be considered refill, it should be a significant posistive change of volume so on 8/14, it would be considered that it is only a small fluctuation on the reading. Generally, a change of more than 400 volume would be considered as a refill.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Human]

@lbendlin Thank you for the advice. I have revise my inquiry as stated above. Please let me know if there's any other issues.