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 activc...
  • 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
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Hi sdaniels

 

Please could you illustrate what you are trying to achieve?

sdaniels's avatar
sdaniels
Regular Visitor
8 years ago

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's avatar
    pxg08680
    Icon for Resolver III rankResolver III
    8 years ago

    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]

            )