Finding the highest Date in a record

[sdaniels]

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]

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]

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

@pxg08680

Thanks. I had missed this point

[Zubair_Muhammad · Accepted solution]

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
)

[sdaniels]

Thank you very much, this worked great for computing the proper number of days.  How would a column or measure be changed to record this new date as a displayable field?  I tried to reverse engineer the formula to give me the findings of the 'MyDates' but to no avail.

Scott

[Zubair_Muhammad]

Hi @sdaniels

Please could you illustrate what you are trying to achieve?

[sdaniels]

The formula you supplied worked great for computing the number of days.  But now the team is asking for to display which date was used to show the start date, along side the new # of days. So I was wondering if its easy to tweak the new formula to not only show the number of days, but also the date it was determining was the highest of the 3.

Thank you - Scott

[pxg08680]

@sdaniels

From @Zubair_Muhammad post

try doing this to get max date of the first three columns.

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]

        )

[pxg08680]

@Zubair_Muhammad

This is working perfectly.

Thank you.

This should be marked as SOLUTION.

[Zubair_Muhammad]

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