DAX formula to Calculate daily consumption from cumulative data - Power BI

Question

I am a new user of power bi visuals and to Dax formulas,

I have a dataset logged from the server for readings of a electric meter and it's logged every minute, I want to write a Dax formula / measure to calculate the daily consumption as the data is accumulative from previous value like the below:

Time Stamp                           Value 
01-Jan-21 00:00:00                   5
01-Jan-21 00:01:00                   8
01-Jan-21 00:02:00                   9

01-Jan-21 23:59:00                   11
02-Jan-21 00:00:00                   15

So i want to calculate the consumption from the first value of 2nd Of Jan - first value of 1st of Jan so it should be 02-Jan-21 00:00:00 - 01-Jan-21 00:00:00 and So on for the next days.

How to get the minimum value of the day and subtract from previous minimum value.

msta42a msta42a · Accepted Answer · 2021-08-28T19:25:11

You can do that this way:

SomeMeasure = 
var __currentDateMinVal = CALCULATE( min(Sheet2[Value]), filter(ALL(Sheet2),FORMAT(SELECTEDVALUE(Sheet2[Time Stamp]),"yyyy-MM-dd") = FORMAT((Sheet2[Time Stamp]),"yyyy-MM-dd") ))
var __prevDateMinVal = CALCULATE( min(Sheet2[Value]), filter(ALL(Sheet2),FORMAT(SELECTEDVALUE(Sheet2[Time Stamp])-1,"yyyy-MM-dd") = FORMAT((Sheet2[Time Stamp]),"yyyy-MM-dd") ))
return
__currentDateMinVal - __prevDateMinVal

I use: FORMAT(SELECTEDVALUE(Sheet2[Time Stamp]),"yyyy-MM-dd") to check current day and compare this with whole table. ALL inside FILTER remove context filter (row filter in this example).

DAX formula to Calculate daily consumption from cumulative data - Power BI

1 Answers