Forum Discussion

sdaniels's avatar
sdaniels
Regular Visitor
8 years ago
Solved

Finding the highest Date in a record

Community,

 

I'm stumped on this one.  I have a series of projects that have 4 date records associated with them.  I need to create a measurement that computes the number of days between the activcation date and one of the 3 previous dates, but always selecting the highest date of the 3 values.  If the measurement is under 5 days it is a bonus for sales.  Sometimes there are missing values in some of the dates.

 

How can I do this without creating a massive IF nested loop calculation?

 

Ex Date

Sold Date         Advised Date        Quoted date      Activation Date       # of days to Activation

10/1/2017          10/5/2017             10/3/2017          10/10/2017             5

11/1/2017                                        11/6/2017          11/10/2017            4 (Bonus)

11/1/2017           11/16/2017           11/2/2017          11/10/2017            8  (advised is past the activation)

 

Thanks for the help,

 

Scott

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi sdaniels and pxg08680

     

    How abou this calculated column

     

    =
DATEDIFF (
    MAXX (
        FILTER (
            UNION (
                ROW ( "MyDates", CALCULATE ( VALUES ( Table1[Sold Date] ) ) ),
                ROW ( "MyDates", CALCULATE ( VALUES ( Table1[Advised Date] ) ) ),
                ROW ( "MyDates", CALCULATE ( VALUES ( Table1[Quoted date] ) ) )
            ),
            [MyDates] <= Table1[Activation Date]
        ),
        [MyDates]
    ),
    Table1[Activation Date],
    DAY
)

10 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Hi sdaniels

     

    One of the ways. Add this Calculated Column

     

    =
ABS (
    MAX ( MAX ( Table1[Sold Date], Table1[Advised Date] ), Table1[Quoted date] )
        - Table1[Activation Date]
)
    • pxg08680's avatar
      pxg08680
      Icon for Resolver III rankResolver III
      8 years ago

      Zubair_Muhammad

      Just to add up some information, the solution you had given is working fine but when you look at sdaniels 's data example,

      3rd row difference needs to be 8. But according to your solution it gives 6. By default it is taking max date but it should take max date which is less than ActivationDate. So it should be 11/10/2017 - 11/2/2017 which is 8. As per the above data example.

       

      I have a similar kind of issue. This might help me.

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Hi sdaniels

       

      Another way.

       

      This is so convenient if you have many columns

       

      1) Go to Query Editor>> Select the "Date Columns" whose Maximum is to be determined

      2)Go to Add Column>>> Date >>>> Latest