Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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...
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
3 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

yx
12/31/20161467148258
1/10/2017 260
5/6/2017 264
10/11/2017 270
12/31/20171486401274
2/2/2018 275
3/4/2018 278
8/10/2018 282
12/31/20181498969284
2/19/2019 290
3/7/2019 264
8/11/2019 300
12/31/20191524506304
  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      3 years ago

      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?