Forum Discussion
Helper IWater Filter forecast scenario
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
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 datevar max_1 = CALCULATE(VALUES(Reading_Table[readingDate]),Reading_Table[Rank_date]=1)
//previous last reading datevar max_2 = CALCULATE(VALUES(Reading_Table[readingDate]),Reading_Table[Rank_date]=2)
//difference value between last and previous last readingvar 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 consumptionvar avg_reading_daily = max_1_Value / (max_1-max_2)
returnremaining_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 assistancethankslet met know if above suggestion help you.
3 Replies
- negi007
Community Champion
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 datevar max_1 = CALCULATE(VALUES(Reading_Table[readingDate]),Reading_Table[Rank_date]=1)
//previous last reading datevar max_2 = CALCULATE(VALUES(Reading_Table[readingDate]),Reading_Table[Rank_date]=2)
//difference value between last and previous last readingvar 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 consumptionvar avg_reading_daily = max_1_Value / (max_1-max_2)
returnremaining_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 assistancethankslet met know if above suggestion help you.
