Water Filter forecast scenario

[gdbaerg]

Hi there, I'd very much appreciate some help with the following scenario. 

The table/chart below shows readings taken from a digital readout on a water filter over a period of several weeks (note: the interval between readings can vary - it is not always 14 days). 

I'd like to use these readings:
A/ to predict when "readingValue" will hit 0
B/  to trigger a power automate flow when it hits a set value (ie. "readingValue" hits is less than 200 litres remaining)  

Here's a table showing the sample values above:

readingDate	readingValue	readingDifference	priorSessionreading	numberDaysbetween
9/02/20 0:00	2555			0
9/16/20 0:00	2027	-528	2555	14
9/30/20 0:00	1591	-436	2027	14
10/14/20 0:00	1142	-449	1591	14

Any help would be greatly appreciated.

Thanks,
Greg

[negi007 · Accepted solution]

@gdbaerg so there are two parts to your problem.

First of all we will create a column to rank readingdate this will be used in our calculation

Rank_date = RANK.EQ(Reading_Table[readingDate],Reading_Table[readingDate],DESC)

1. To predict in how many days reading value will be zero. You can use below measure to predict in how many days reading value will be zero.

Predict_days_to_zero =

//last max reading date

var max_1 = CALCULATE(VALUES(Reading_Table[readingDate]),Reading_Table[Rank_date]=1)

//previous last reading date

var max_2 = CALCULATE(VALUES(Reading_Table[readingDate]),Reading_Table[Rank_date]=2)

//difference value between last and previous last reading

var max_1_Value = -1 * (LOOKUPVALUE(Reading_Table[readingDifference],Reading_Table[readingDate],max_1))

var remaining_value = LOOKUPVALUE(Reading_Table[readingValue],Reading_Table[readingDate],max_1)

//calculate daily consumption

var avg_reading_daily = max_1_Value / (max_1-max_2)

return

remaining_value/avg_reading_daily

in the above, dax i have used two recent reading to first calculate average daily consumption/reading then using the latest reading to know in how many days it will be zero. using above calculation my answer was 36 days.

2. To create automate flow, you may refer to below links for assistance

https://docs.microsoft.com/en-us/power-automate/get-started-logic-flow

https://powerapps.microsoft.com/en-us/automate-processes/

thanks

let met know if above suggestion help you.

[negi007 · Accepted solution]

@gdbaerg so there are two parts to your problem.

First of all we will create a column to rank readingdate this will be used in our calculation

Rank_date = RANK.EQ(Reading_Table[readingDate],Reading_Table[readingDate],DESC)

1. To predict in how many days reading value will be zero. You can use below measure to predict in how many days reading value will be zero.

Predict_days_to_zero =

//last max reading date

var max_1 = CALCULATE(VALUES(Reading_Table[readingDate]),Reading_Table[Rank_date]=1)

//previous last reading date

var max_2 = CALCULATE(VALUES(Reading_Table[readingDate]),Reading_Table[Rank_date]=2)

//difference value between last and previous last reading

var max_1_Value = -1 * (LOOKUPVALUE(Reading_Table[readingDifference],Reading_Table[readingDate],max_1))

var remaining_value = LOOKUPVALUE(Reading_Table[readingValue],Reading_Table[readingDate],max_1)

//calculate daily consumption

var avg_reading_daily = max_1_Value / (max_1-max_2)

return

remaining_value/avg_reading_daily

in the above, dax i have used two recent reading to first calculate average daily consumption/reading then using the latest reading to know in how many days it will be zero. using above calculation my answer was 36 days.

2. To create automate flow, you may refer to below links for assistance

https://docs.microsoft.com/en-us/power-automate/get-started-logic-flow

https://powerapps.microsoft.com/en-us/automate-processes/

thanks

let met know if above suggestion help you.

[gdbaerg]

Thanks a lot for the help @negi007.  How did you define the Rank_date field in your measure?   

[negi007 · Accepted solution]

@gdbaerg Sorry I missed something yes we need to create one calculated column like below

Rank_date = RANK.EQ(Reading_Table[readingDate],Reading_Table[readingDate],DESC)

this column will be used in our calculation