Forum Discussion

7 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor

    MOhammedaldb 

    Do you really want to calculate CreatedOn minus today? You get a of lot negative values.

     

    And there is something wrong with your dates. Some of them are in the future.

     

    • MOhammedaldb's avatar
      MOhammedaldb
      Copper Contributor
      Hello Detlef, regarding the " CreatedOn minus today" yes my mistake i switched them around i meant " Today - Created On "
      and regarding the future dates some of them is " Estimated Close date > Est Close Date " it's just a estimate on when the deal will be closed, and regarding the closed deals that are in the future yes indeed your right but this is just an example on how the data is structured if you are saying that the formula wont work because of this i will update the dataset now thank you
      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor

        MOhammedaldb 

        Then it is simply:

        =MAX([@[Close Date]],TODAY())-[@[Created on]]

        Future dates will have negative values.

         

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    MOhammedaldb 

    w/modified column used in the math:

    =MAP(Table1[Close Date],Table1[Created on],Table1[Modified On],LAMBDA(h,i,j,IF(ISBLANK(h),j-i,h-i)

    w/TODAY() function used in the math (This changes the results considerably):

    =MAP(Table1[Close Date],Table1[Created on],LAMBDA(h,i,IF(ISBLANK(h),TODAY()-i,h-i)))

     

    If this is what you're looking for and want to put the 'timeline' in the table, then we'd go with a logical IF and forget MAP.  Outside of the table, MAP's results are dynamic based on the table.

    • MOhammedaldb's avatar
      MOhammedaldb
      Copper Contributor
      wow thank you so much Patrick i tried the second formula and it worked perfectly, but just a question how will the formula differ if we use IF instead of MAP and yes I'm intending to use your formula inside the same table.
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        If the formula is going in the table, then go with IF. The results of MAP spill and tables are scalar-based (1 cell, 1 formula, 1 return).

Resources