cancel
Showing results for
Search instead for
Did you mean:  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) 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. 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 Red Any Idea how to accomplish that?

Thanks in advance,

Rui

1 ACCEPTED SOLUTION  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. 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 2 REPLIES 2  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. 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   Helper I

Thank you @MFelix , it works perfectly  🙂  ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group! #### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks #### 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
Users online (10,220)