How to deal with 5 minute intervals that are cumulative?

[norrisc]

I have light data going back a few years, with 5-minute intervals. My problem is that it is cumulative, so if I sum up the daily total I get a huge number. It resets to 0 each day.

I'm new with power BI, but I think the best solution would be to have a column or measure that is subtracting the top row from the bottom. Do you agree? If so, how can I do that?

[norrisc]

@CahabaData @SivaMani

Apologies.  If you look at the 2nd row in my screenshot there - I want the output to be 8 (791-783)

I'm struggling to do this in Power BI!

[CahabaData]

you would want to use the LOOKUPVALUE function, and as it seems you don't have an index column - the basis for look up would be the date/time less 5 minutes

[Interkoubess]

Hi @norrisc,

First of all I added an Index column (named Ind) to the data and then I created this column with this formula ( I called my table DataFin:

PrevCDT =
VAR CDTPrev =
    SUMX (
        FILTER ( DataFin, EARLIER ( DataFin[Ind] ) = DataFin[Ind] + 1 ),
        DataFin[CDT]
    )
RETURN
    IF ( ISBLANK ( CDTPrev ), BLANK (), DataFin[CDT] - CDTPrev )

You can see my outcomes below:

[CahabaData]

Your post is not clear.  If you refer to the field Cumulative Daily Light - then rather than sum perhaps you wish to take the difference between the first and last row/record.

[SivaMani]

Hi @norrisc,

What is your expected output?