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