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 more than 30 days, then it's "Late". How do I calculate the ones that are running late that don't have the completed date but only the start date. We can use today's date to calculate how late it is. 
 
Below is the code I have so far. 
 
Q Timeline = Switch(true(),ISBLANK('FPS selection'[O to C]), "No Value",                                                                                               'FPS selection'[O to C] <= 30 , "On Time",
'FPS selection'[O to C] > 30, "Late")
 
Thank you for your help. 
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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi dineshj23 ,

     

    Try below formula:

    Q Timeline = 
var completed = if(isblank([completed date]),today(),[completed date])
var date_diff = datediff([start date],[completed date],day)
return
IF(date_diff>30,"late","on time")

     

    Best Regards,

    Jay

    • dineshj23's avatar
      dineshj23
      Helper I
      3 years ago

      Hi Anonymous Thank you for your reply. 

      What about the ones that don't have a completed date entered but are already running late? I would call this variable "Pending Late". 

      • dineshj23's avatar
        dineshj23
        Helper I
        3 years ago

        Anonymous Hi. I think you might ahve not finished writing the complete code. 

  • 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