Convert cumulative data to non-comulative data

[flintstone]

I have real time energy consumption data with a timestamp streaming from a websocket.

16/7/2020 00:00.00     6

16/7/2020 00:00.00     14

16/7/2020 00:00.00     17

16/7/2020 00:00.00     34

I would like to get Powerbi to show the non-cumulative values as show below in the lat column

16/7/2020 00:00.00     6        6

16/7/2020 00:00.00     14     8

16/7/2020 00:00.00     17     3

16/7/2020 00:00.00     34    17

Any help would be appreciated.

[flintstone]

Regretably both set of codes in Power bi did not work. I have now enclosed the larger data in the link

https://www.dropbox.com/s/kvagdhivzvhzq1w/energy.pbix?dl=0

[flintstone]

Hi! Any ideas/solutions so far?

[Ashish_Mathur]

Hi,

What problem did you face with my formula?

[flintstone]

Hi! I have applied your formula and the results were not consistent. Where there were missing data, I get negative results. When there were pre and post postive numbers, it should show the difference. But it did not.

You can view it is the dropbox link I had attached.

[Ashish_Mathur]

Hi,

Something strange happening in that file.  When i paste my formula in a calculated column, i get a message saying "Working on it".  I do not know why this is happening.  Sorry.

[Ashish_Mathur]

Hi,

This calculated column formula works

Column = Casting[Incoming 33kv]-LOOKUPVALUE(Casting[Incoming 33kv],Casting[DateTimeStamp],CALCULATE(MAX(Casting[DateTimeStamp]),FILTER(Casting,Casting[DateTimeStamp]<EARLIER(Casting[DateTimeStamp]))))

Hope this helps.

[mahoneypat]

Assuming the time component of your DateTime values are not all zeros, you can use an expression like this to get your result in a measure.  Use the measure in a table visual with your DateTime column.  Let me know if you need the column version instead.

Actual Consumption =
VAR __thisvalue =
    MIN ( Table[EnergyConsumed] )
VAR __thisdatetime =
    MIN ( Table[DateTime] )
VAR __prevdatetime =
    CALCULATE (
        MAX ( Table[DateTime] ),
        ALL ( Table[DateTime] ),
        Table[DateTime] < __thisdatetime
    )
VAR __prevvalue =
    CALCULATE (
        MIN ( Table[EnergyConsumed] ),
        ALL ( Table[DateTime] ),
        Table[DateTime] = __prevdatetime
    )
RETURN
    __thisvalue - __prevvalue

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[flintstone]

Thanks, Pat. I keyed in the code but it ran into some errors as highlighted in red

 

[mahoneypat]

I had a typo in my original post, with a variable that referenced itself.  I edited that post.  FYI that what I provided was for a measure expression to be used in a table visual, not for a calculated column.

Regards,

Pat

[flintstone]

We are currently running the formula on the entire dataset. If it is agreeable by all, I would like to keep this conversation open until we have the confirmation that the formula solves the problem. This will not take very long.

[v-lili6-msft]

hi @flintstone 

Could you please share your sample pbix file for us to have a test, that will be a great help.

Regards,

Lin

[flintstone]

How do I upload the pbix file? I tried but there is a error message "The file type (.pbix) is not supported"

[v-lili6-msft]

hi  @flintstone 

For your case, just create a new column as below:

Column =
VAR __prevdatetime =
    CALCULATE (
        MAX ( Casting[DateTimeStamp] ),
        FILTER ( Casting, Casting[DateTimeStamp] < EARLIER ( Casting[DateTimeStamp] ) )
    )
RETURN
    Casting[Incoming 33kv]
        - CALCULATE (
            SUM ( Casting[Incoming 33kv] ),
            FILTER ( Casting, Casting[DateTimeStamp] = __prevdatetime )
        )

Result:

Regards,

Lin

[flintstone]

https://www.dropbox.com/s/nsc99rndezyqcud/Casting%20Data%202.pbix?dl=0

[flintstone]