Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 🙂
Solved! Go to Solution.
Hi @DamGils,
Please refer to my test .pbix file. The table 'TB2' is the final output.
Regards,
Yuliana Gu
@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?
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 🙂
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.
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.
Hi @DamGils,
Please refer to my test .pbix file. The table 'TB2' is the final output.
Regards,
Yuliana Gu
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-/
User | Count |
---|---|
89 | |
85 | |
66 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |