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 )
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
Icon for Community Champion rankCommunity 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 )