y=mx+c

[Anonymous]

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

[v-zhangti]

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.

[Syndicate_Admin]

Any update on this please? Really appricate if you can help me

[Anonymous]

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 · Accepted solution]

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_Admin]

Thanks for reply. For M1 and C1, it shows minx and maxx as the number is incremental or decremental, what if it is not incremental or decremental, how can we calculate M1 and C1, please?

[Anonymous]

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. 

[HoangHugo]

Hi

New Table = SUMMARIZE ([data table], [Date column],[Y column],[X column])