Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX for date difference

Hi,

I have 2 dates "Start Date" and "End Date", I have to create a new column with the difference of these 2 dates as highlighled below.

I want the new column to show up as 4/1/2021-4/30/2021=1 and 7/1/2021-9/30/2021=3  

I am using DAX DATEDIFF(Opportunity[Start Date].[Date],Opportunity[End Date].[Date],MONTH) for this but its not including the current start month for 4/1/2021-4/30/2021=0 but I want it to show up as 1.
 
Please help.
 
 
Need Help
  • FrankAT's avatar
    FrankAT
    5 years ago

    Hi Anonymous ,

    so here is a third variation:

     

     

    Difference in Month = 
VAR _StartLessThanEnd =
IF (
    OR ( 'Table'[Start], 'Table'[End] ) = BLANK (),
    BLANK (),
    DATEDIFF ( 'Table'[Start], 'Table'[End], MONTH ) + 1
)
VAR _EndLessThanStart =
IF ( 
    OR ( 'Table'[Start], 'Table'[End] ) = BLANK (),
    BLANK (),
    DATEDIFF ( 'Table'[End], 'Table'[Start], MONTH ) + 1
)
RETURN
    if ( 'Table'[Start] < 'Table'[End], _StartlessThanEnd, _EndLessThanStart)

     

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

     

7 Replies

  • FrankAT's avatar
    FrankAT
    Community Champion
    5 years ago

    Hi Anonymous 

    adjust your formula like this:

     

     

    = DATEDIFF('Table'[Start],'Table'[End],MONTH) + 1

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi FrankAT,

      I cannot use +1 at the end blanks in start and end dtae columns and they would appear as 1 in my new column.

      • FrankAT's avatar
        FrankAT
        Community Champion
        5 years ago

        Hi Anonymous ,

        use the following solution:

         

         

        Difference in Month =
IF (
    OR ( 'Table'[Start], 'Table'[End] ) = BLANK (),
    BLANK (),
    DATEDIFF ( 'Table'[Start], 'Table'[End], MONTH ) + 1
)

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

  • FrankAT's avatar
    FrankAT
    Community Champion
    5 years ago

    Hi Anonymous 

    if the absolute number in 'Test Term' is correct and you want only to get reed of the negativ sign then use the following measure:

    Difference in Month =
IF (
    OR ( 'Table'[Start], 'Table'[End] ) = BLANK (),
    BLANK (),
    ABS( DATEDIFF ( 'Table'[Start], 'Table'[End], MONTH ) + 1 )
)

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi FrankAT ,

      Thak you for the prompt reply but the value is also wrong for these dates

      start date =01-19-2022 -end date =03-31-2021  =  11 months but its giving -9

       

      • FrankAT's avatar
        FrankAT
        Community Champion
        5 years ago

        Hi Anonymous ,

        so here is a third variation:

         

         

        Difference in Month = 
VAR _StartLessThanEnd =
IF (
    OR ( 'Table'[Start], 'Table'[End] ) = BLANK (),
    BLANK (),
    DATEDIFF ( 'Table'[Start], 'Table'[End], MONTH ) + 1
)
VAR _EndLessThanStart =
IF ( 
    OR ( 'Table'[Start], 'Table'[End] ) = BLANK (),
    BLANK (),
    DATEDIFF ( 'Table'[End], 'Table'[Start], MONTH ) + 1
)
RETURN
    if ( 'Table'[Start] < 'Table'[End], _StartlessThanEnd, _EndLessThanStart)

         

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