cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trovisco
Helper I
Helper I

Calculate Interpolation Value for every Day of the month given as input only Value for 3 days

 

Hello Everyone,

I have a Table "Event duration Prediction" with [Duration] Values for 01.01.2020 , 15.01.2020 and 31.01.2020 (shown below)

B1.jpg

I have a "Dates" Table with [Date] from 01.01.2020 to 31.01.2020 (shown below)  and there is a Date Relationship between both.B2.jpg

Below I plot in X axis my "Dates Table" [Date], and as Y axis my "Event Duration Prediction" [Duration] .

 

My goal is to be able to calculate (Interpolate) the Duration for Every single day in the [Dates] Table (Values shown below as Orange below)

At the moment I only have the 3 Values represented as Redb3.jpg

Any Idea how to accomplish that? 

 

Thanks in advance,

Rui

 

1 ACCEPTED SOLUTION
MFelix
Super User III
Super User III

Hi @trovisco  (Rui),

 

Looking at the blog post you can adapt the code to the following https://community.powerbi.com/t5/Quick-Measures-Gallery/Linear-Interpolation/m-p/330712:

(thank you @Greg_Deckler )

You create a measure with the following syntax:

 

Interpolated Value = 
VAR x3 = MAX(Dates[Date])
VAR match = CALCULATE(MAX('Event Duration Prediction'[Duration]);FILTER('Event Duration Prediction';'Event Duration Prediction'[Date]=x3))
VAR x1 = CALCULATE(MAX('Event Duration Prediction'[Date]);FILTER('Event Duration Prediction';'Event Duration Prediction'[Date]<=x3))
VAR x2 = CALCULATE(MIN('Event Duration Prediction'[Date]);FILTER('Event Duration Prediction';'Event Duration Prediction'[Date]>=x3))
VAR y1 = CALCULATE(MAX('Event Duration Prediction'[Duration]);FILTER('Event Duration Prediction';'Event Duration Prediction'[Date]<=x3))
VAR y2 = CALCULATE(MIN('Event Duration Prediction'[Duration]);FILTER('Event Duration Prediction';'Event Duration Prediction'[Date]>=x3))
RETURN IF(NOT(ISBLANK(match));match;y1 + (x3 - x1) * (y2 - y1)/(x2 - x1))

 

Be aware that the trick is not to have the Date table and Events table related with each other.

interpolado.gif

 

Any questions please tell me.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User III
Super User III

Hi @trovisco  (Rui),

 

Looking at the blog post you can adapt the code to the following https://community.powerbi.com/t5/Quick-Measures-Gallery/Linear-Interpolation/m-p/330712:

(thank you @Greg_Deckler )

You create a measure with the following syntax:

 

Interpolated Value = 
VAR x3 = MAX(Dates[Date])
VAR match = CALCULATE(MAX('Event Duration Prediction'[Duration]);FILTER('Event Duration Prediction';'Event Duration Prediction'[Date]=x3))
VAR x1 = CALCULATE(MAX('Event Duration Prediction'[Date]);FILTER('Event Duration Prediction';'Event Duration Prediction'[Date]<=x3))
VAR x2 = CALCULATE(MIN('Event Duration Prediction'[Date]);FILTER('Event Duration Prediction';'Event Duration Prediction'[Date]>=x3))
VAR y1 = CALCULATE(MAX('Event Duration Prediction'[Duration]);FILTER('Event Duration Prediction';'Event Duration Prediction'[Date]<=x3))
VAR y2 = CALCULATE(MIN('Event Duration Prediction'[Duration]);FILTER('Event Duration Prediction';'Event Duration Prediction'[Date]>=x3))
RETURN IF(NOT(ISBLANK(match));match;y1 + (x3 - x1) * (y2 - y1)/(x2 - x1))

 

Be aware that the trick is not to have the Date table and Events table related with each other.

interpolado.gif

 

Any questions please tell me.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Thank you @MFelix , it works perfectly  🙂

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors