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

  • tamerj1's avatar
    tamerj1
    Community Champion
    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 )