Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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-/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |