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
Community 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
      Community 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
        Administrator
        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?

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

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