cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gdbaerg
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)  

PBIDesktop_QEtuFIkaWe.png

 

Here's a table showing the sample values above:

 

readingDatereadingValuereadingDifferencepriorSessionreadingnumberDaysbetween
9/02/20 0:002555  0
9/16/20 0:002027-528255514
9/30/20 0:001591-436202714
10/14/20 0:001142-449159114

 

Any help would be greatly appreciated.

 

Thanks,
Greg

1 ACCEPTED SOLUTION
negi007
Super User II
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.
negi007_0-1614428672200.png

 

 
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


View solution in original post

3 REPLIES 3
negi007
Super User II
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.
negi007_0-1614428672200.png

 

 
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


View solution in original post

gdbaerg
Frequent Visitor

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

@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
User Groups Public Preview

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.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

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.