Forum Discussion
y=mx+c
I want to use y= mx+c to calculate y on the dates that are in b/w the years. so first how I will calculate "m" and "C"
using given table.
| Date | y | x | calculated column m | calculated comumn C |
| 12/31/2016 | 1467148 | 258 | m = (y3-y2/x3-x2) | y=(m*x)+C |
| 12/31/2017 | 1486401 | 274 | How to use cell level data from y and x column to calculate m | |
| 12/31/2018 | 1498969 | 284 | ||
| 12/31/2019 | 1524506 | 304 | ||
| 12/31/2020 | 1547590 | 322 | ||
| 12/31/2021 | 1577861 | 343 | ||
| 12/31/2022 | 1713452 | 429 | ||
| 12/31/2023 | 1856239 | 493 |
Once thats done, how will I enter value in y in a different table, as it get calculated from the above table.
| Date | y | x |
| 12/31/2016 | 1467148 | 258 |
| 1/10/2017 | 260 | |
| 5/6/2017 | 264 | |
| 10/11/2017 | 270 | |
| 12/31/2017 | 1486401 | 274 |
| 2/2/2018 | 275 | |
| 3/4/2018 | 278 | |
| 8/10/2018 | 282 | |
| 12/31/2018 | 1498969 | 284 |
| 2/19/2019 | 290 | |
| 3/7/2019 | 264 | |
| 8/11/2019 | 300 | |
| 12/31/2019 | 1524506 | 304 |
Hi, Anonymous
You can try the following methods.
Column:
m = LOOKUPVALUE('Table'[m],'Table'[Date],[Date])C = LOOKUPVALUE('Table'[C],'Table'[Date],[Date])M1 = MINX(FILTER('Table (2)',EARLIER('Table (2)'[Date])<=[Date]),[m])C1 = MAXX(FILTER('Table (2)',EARLIER('Table (2)'[Date])<=[Date]),[C])Y1 = [M1]*[x]+[C1]You said: "for example for 1/10/2017 y = (1203.31 * 260) + 1156693.38 =1469553.98".
It is not exactly equal to this value because both M1 and C1 have hidden decimals.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
HoangHugoSolution Specialist
Hi
New Table = SUMMARIZE ([data table], [Date column],[Y column],[X column])
I didn't get that. First we need to calculate m on the first table. y value from current cell to previous cell and x value from current to previous cell. Can you help me with that from first table. and once that is calculated then need to insert y values on the dates that dont it based on the top table when there is no common lookup value.
v-zhangtiCommunity Support
Hi, Anonymous
You can try the following methods.
Column:Previous y = MAXX(FILTER('Table',[Date]<EARLIER('Table'[Date])),[y])Previous x = MAXX(FILTER('Table',[Date]<EARLIER('Table'[Date])),[x])m = IF([Previous x]<>BLANK()&&[Previous y]<>BLANK(),DIVIDE([y]-[Previous y],[x]-[Previous x]))C = IF([m]<>BLANK(),[y]-([m]*[x]))Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
that's half of my question. now with the data you provided we need to fill Y = MX+C in another table where dates are within those years ending values to show the progress of Y. Can you help me with that please?
so if the date is in b/w 12/31/2016 && 12/31/2017, it will take the X multply m from 12/31/2017 and add C from 12/31/217 to it
for example for 1/10/2017 y = (1203.31 * 260) + 1156693.38 = 1469553.98
Date
y x 12/31/2016 1467148 258 1/10/2017 260 5/6/2017 264 10/11/2017 270 12/31/2017 1486401 274 2/2/2018 275 3/4/2018 278 8/10/2018 282 12/31/2018 1498969 284 2/19/2019 290 3/7/2019 264 8/11/2019 300 12/31/2019 1524506 304 - v-zhangti
Hi, Anonymous
You can try the following methods.
Column:
m = LOOKUPVALUE('Table'[m],'Table'[Date],[Date])C = LOOKUPVALUE('Table'[C],'Table'[Date],[Date])M1 = MINX(FILTER('Table (2)',EARLIER('Table (2)'[Date])<=[Date]),[m])C1 = MAXX(FILTER('Table (2)',EARLIER('Table (2)'[Date])<=[Date]),[C])Y1 = [M1]*[x]+[C1]You said: "for example for 1/10/2017 y = (1203.31 * 260) + 1156693.38 =1469553.98".
It is not exactly equal to this value because both M1 and C1 have hidden decimals.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Syndicate_AdminAdministrator
Any update on this please? Really appricate if you can help me