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 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's avatar
sdaniels
Regular Visitor
8 years ago

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

                )