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

 

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

      pxg08680

       

      Thanks. I had missed this point:smileyembarrassed:

      • 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
)
  • 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