Linear interpolation with differents counters

[DamGils]

Hello world,

It has been two days since I have a problem to handle my database, I really need help. My problem concerns the linear interpolation between two values, I'll explain :

My database is like this,

Date	Countid	Value
15/05/2017	46	425
15/05/2017	47	14
15/05/2017	48	356
18/05/2017	46	600
18/05/2017	47	26
18/05/2017	48	563

As you can see, I have several meters at several times. Here I want to specify that the number of meters can be variable, just like the number of date. To estimate the consumption, I need to linearized between two statements. Thanks to the difference between two days, I so wish to obtain the consumption, like that :

Date	CountId	Value	Consumption
15/05/2017	46	425
16/05/2017	46	483	58
17/05/2017	46	542	58
18/05/2017	46	600	58
15/05/2017	47	14
16/05/2017	47	18	4
17/05/2017	47	22	4
18/05/2017	47	26	4
15/05/2017	48	356
16/05/2017	48	425	69
17/05/2017	48	494	69
18/05/2017	48	563	69

Didi anybody think he can help me ?

Thank you in advance for your help and do not hesitate to ask me questions if I was not rather precise,

Have fun 🙂 

[Greg_Deckler]

@DamGils- I'm not understanding the linear interpolation aspect of this. Are you trying to calculate the Consumption column? If not that, what is the expected output?

[DamGils]

HI @Greg_Deckler - Thanks a lot for your answer ! I'll re-explain to you. So, my database is like this :

Date	Countid	Value
15/05/2017	46	425
15/05/2017	47	14
15/05/2017	48	356
18/05/2017	46	600
18/05/2017	47	26
18/05/2017	48	563

I try to linearized the values between two dates for each count, like this for CountId 46 for example:

Date	CountId	Value
15/05/2017	46	425
16/05/2017	46	x3
17/05/2017	46	x4
18/05/2017	46	600

Concretely I look for both line in red and I want to calculate x3 and x4. They can be calculate like this :

x3=425+((600-425)/(DATEDIFF(18/05/2017;15/07/2017)

As you understood, the final result fo which I try to obtain is the consumption. The latter is reckoned by making the valuable difference between two dates, as this :

Date	CountId	Value	Consumption
15/05/2017	46	425
16/05/2017	46	483	z1
17/05/2017	46	542	z2
18/05/2017	46	600	z3

z1 = 483 - 425 = 58

Finally, I want to reply this operation for each counters automatically, and obtain this :

Date	CountId	Value	Consumption
15/05/2017	46	425
16/05/2017	46	483	58
17/05/2017	46	542	58
18/05/2017	46	600	58
15/05/2017	47	14
16/05/2017	47	18	4
17/05/2017	47	22	4
18/05/2017	47	26	4
15/05/2017	48	356
16/05/2017	48	425	69
17/05/2017	48	494	69
18/05/2017	48	563	69

(all reds values are calculated)

I hope that the explanation of my problem is more clear this time,

Thank you in advance for your answers 🙂

[Greg_Deckler]

Yes, much more clear, I appreciate it. And I appreciate you providing the data in a format that I can copy and paste. I should have some time to work on this today but this is going to be a bit involved to work through.

[DamGils]

Good, It was the purpose. I understand the complexity of my request, it's exactly for that it has been now three days whom I am on the subject. I wait for your return and thank you for your help.

[v-yulgu-msft · Accepted solution]

Hi @DamGils,

Please refer to my test .pbix file. The table 'TB2' is the final output.

Regards,

Yuliana Gu

[Greg_Deckler]

I have a Quick Measure for Linear Interpolation here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Linear-Interpolation/m-p/330712

And a blog article on it here:

https://www.linkedin.com/pulse/linear-interpolation-power-bi-greg-deckler-microsoft-mvp-/