Forum Discussion

dineshj23's avatar
dineshj23
Helper I
3 years ago
Solved

Calculating the difference between two dates

Hi. I have two dates in my workbook; start and completed date. I am calculating the difference between the two dates. If the difference is less than 30 days, it's "On Time". If the difference is mor...
Need Help
Show and Tell
  • TomMartens's avatar
    TomMartens
    3 years ago

    Hey dineshj23 ,

     

    the following screenshot shows my sample data and also the result:

    Here is the DAX statement to create the Calculated Column:

    timeline = 
var StartDate = 'Table'[start date]
var CompletedDateIsMissing  = IF( ISBLANK( 'Table'[completed date] ) , 1 , 0)
return
SWITCH(
    CompletedDateIsMissing
    , 0
        , var __DateDiff = DATEDIFF( StartDate , 'Table'[completed date] , DAY )
        return
        IF( __DateDiff < 30 , "On time" , "Late" )
    , 1
        , var DateToday = TODAY() 
        var __DateDiff = DATEDIFF( StartDate , DateToday , DAY )
        return
        IF( __DateDiff < 30 , "On time" , "Pending Late" )
)

    I branch the decision-making by using SWITCH (completed date is missing or not). In both cases I calculate the number of days and return a value.

    Hopefully, this what you are looking for.

     

    Regards,
    Tom

TomMartens's avatar
TomMartens
Super User
3 years ago

Hey dineshj23 ,

 

the following screenshot shows my sample data and also the result:

Here is the DAX statement to create the Calculated Column:

timeline = 
var StartDate = 'Table'[start date]
var CompletedDateIsMissing  = IF( ISBLANK( 'Table'[completed date] ) , 1 , 0)
return
SWITCH(
    CompletedDateIsMissing
    , 0
        , var __DateDiff = DATEDIFF( StartDate , 'Table'[completed date] , DAY )
        return
        IF( __DateDiff < 30 , "On time" , "Late" )
    , 1
        , var DateToday = TODAY() 
        var __DateDiff = DATEDIFF( StartDate , DateToday , DAY )
        return
        IF( __DateDiff < 30 , "On time" , "Pending Late" )
)

I branch the decision-making by using SWITCH (completed date is missing or not). In both cases I calculate the number of days and return a value.

Hopefully, this what you are looking for.

 

Regards,
Tom

  • dineshj23's avatar
    dineshj23
    Helper I
    3 years ago

    Hi TomMartens, How can I see if there's completed date missing but it's less than 30 days from Today and to label it as "Pending On time? 

    Thank you. 

    • TomMartens's avatar
      TomMartens
      Super User
      3 years ago

      Hey dineshj23 ,

       

      just change this part

          , 1
        , var DateToday = TODAY() 
        var __DateDiff = DATEDIFF( StartDate , DateToday , DAY )
        return
        IF( __DateDiff < 30 , "On time" , "Pending Late" )
)

      to this

          , 1
        , var DateToday = TODAY() 
        var __DateDiff = DATEDIFF( StartDate , DateToday , DAY )
        return
        IF( __DateDiff < 30 , "Pending On time" , "Pending Late" )
)

       

      Regards,

      Tom