Calculating Consumption from day before

[Osama14]

Hi, 

I have a data set gathering readings from various meters per mintue in a table called historian, the readings are accumlated, i am trying to get the daily consumption by getting the difference of start of day 2 and Day 1, which should be:  "03-07-21 00:00:00 - 02-07-21 00:00:00" 

I have used the below formula by getting the minimum of the day.  

Meter 1 Consumption = 
IF(
    ISFILTERED('Historian'[Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    VAR __PREV_Day =
        CALCULATE(
            MIN('Historian'[QC Meter 1]),
            DATEADD('Historian'[Date].[Date], -1, DAY)
        )
    RETURN
MIN('Historian'[QC Meter 1]) - __PREV_DAY)

I am having 2 problems, 

1-  a total value showing for previous day in 1st row  - Note that the days in data starts on the 2nd of july and no data for 1st.  

2- The Meter sometimes reset when it reaches 9,999,999 as below , so taking the value minimum value of that day will not work.

What will be the best way to deal with this and get the accurate daily consumption even if meter resets.

any advice is much appreciated. 

[Osama14 · Accepted solution]

I have managed to find a work around whenever the meter gets reset, by having an If condition and adding the maximum reading number to the last value and subtracting the next day reading. 

Thanks 

[v-shex-msft]

Hi @Osama14,

Thanks for your sharing.😊

I think this workaround will be helpful if others faced a similar scenario.

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @Osama14,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[CNENFRNL]

Use the following pattern to extract the latest reading in a day,

Latest Reading =
MAXX( TOPN( 1, Historian, Historian[DateTime] ), Historian[Reading] )

[Osama14]

Hi, Thank you for your response 

but your formula will get the latest reading or max per the day which accumlated from the day before, i need to have the difference between day 2 and day 1.  I need only the consumption happened on the day. 

Thanks