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

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.

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