cancel
Showing results for
Search instead for
Did you mean: Frequent Visitor

## Water 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

1 ACCEPTED SOLUTION  Super User II

@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

thanks
let met know if above suggestion help you.

Did I answer your question? Mark my post as a solution!
Appreciate your Kudos

Proud to be a Super User!

Follow me here

3 REPLIES 3  Super User II

@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

thanks
let met know if above suggestion help you.

Did I answer your question? Mark my post as a solution!
Appreciate your Kudos

Proud to be a Super User!

Follow me here Frequent Visitor

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

@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

Did I answer your question? Mark my post as a solution!
Appreciate your Kudos

Proud to be a Super User!

Follow me here  ## Helpful resources

Announcements #### Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview. #### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings. Top Solution Authors
Top Kudoed Authors
Users online (3,661)