Forum Discussion

cpereyra's avatar
cpereyra
Helper I
5 years ago
Solved

Date difference between dates in same column

Hi All,   I need to calculate the date difference between dates in the same column. I'm using the dax below but it is calculating incorrectly I get all 1s. Dates Between Prospects = DATEDIF...
  • FrankAT's avatar
    FrankAT
    5 years ago

    Hi Greg_Deckler 

    i have used a slightly different formula, but your pattern works well:

     

     

    Difference = 
    VAR _CurrentDate = 'Table'[Extra_Fields.Log_MS_Date_Started]
    VAR _PreviousDate = 
        MAXX(
            FILTER(
                'Table',
                'Table'[Extra_Fields.Log_MS_Date_Started] < EARLIER('Table'[Extra_Fields.Log_MS_Date_Started])
            ),
            'Table'[Extra_Fields.Log_MS_Date_Started]
        )
    RETURN
       IF(_PreviousDate = BLANK(), 0, _CurrentDate - _PreviousDate)

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    cpereyra - If I had to guess, you are probably not including all of the filtering criteria that you need to. Like whatever that field is just to the left of your date field. Your filter criteria needs to include all of the row columns that you want to "group" together.

     

    So, like:

    FILTER(
      'Table',
      [Column] = EARLIER([Column]) &&
      [Column1] = EARLIER([Column1])
    )