Forum Discussion

aslam-ansari's avatar
aslam-ansari
Frequent Visitor
3 years ago
Solved

Help with DAX calculation: Finding END Date

More equipment is listed in the equipment column, which I filter out to make sense of.

 

 

The result should be like this:

  • tamerj1's avatar
    tamerj1
    3 years ago

    aslam-ansari 
    My mistake again

    End Date =
VAR CurrentDate = 'Table'[Start Date]
VAR CurrentEquipmentDates =
    CALCULATETABLE (
        VALUES ( 'Table'[Start Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Equipment] )
    )
VAR NextDate =
    MINX (
        FILTER ( CurrentEquipmentDates, 'Table'[Start Date] > CurrentDate ),
        'Table'[Start Date]
    )
RETURN
    IF ( NextDate = BLANK (), TODAY (), NextDate - 1 )

5 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi aslam-ansari 

    please try

    End Date =
    VAR CurrentDate = 'Table'[Start Date]
    VAR CurrentEquipmentDates =
    CALCULATETABLE (
    VALUES ( 'Table'[Start Date] ),
    ALLEXCEPT ( 'Table', 'Table'[Equipment] )
    )
    VAR NextDate =
    MAXX (
    FILTER ( CurrentEquipmentDates, CurrentEquipmentDates > CurrentDate ),
    CurrentEquipmentDates
    )
    RETURN
    IF ( NextDate = BLANK (), TODAY (), NextDate - 1 )

  • aslam-ansari's avatar
    aslam-ansari
    Frequent Visitor
    3 years ago

    hai tamerj1 

    Getting error.

     

    My actual table looks like this. 

     

    In that scenario, take into account time. Some equipment status changes even occur on the same date. The end date in these circumstances should be the same date up to 3:11:06 PM, and thereafter it should be TODAY().

     

     

     

     

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      aslam-ansari 
      Apologies, That was a typo mistake. I was typing on the phone so I miussed up. Please try

      End Date =
VAR CurrentDate = 'Table'[Start Date]
VAR CurrentEquipmentDates =
    CALCULATETABLE (
        VALUES ( 'Table'[Start Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Equipment] )
    )
VAR NextDate =
    MAXX (
        FILTER ( CurrentEquipmentDates, 'Table'[Start Date] > CurrentDate ),
        'Table'[Start Date]
    )
RETURN
    IF ( NextDate = BLANK (), TODAY (), NextDate - 1 )
      • aslam-ansari's avatar
        aslam-ansari
        Frequent Visitor
        3 years ago

        Thank you for the solution tamerj1 

        Something missing in Dax!
        After the first-row same-date return on all dates, the equation still needs to be changed. This only applies to one piece of equipment. Every piece of equipment has this issue.