Calculating the difference between two dates

[dineshj23]

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. 

[TomMartens · Accepted solution]

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]

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]

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

[Anonymous]

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]

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]

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

[Anonymous]

Hi @dineshj23 ，

--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. 

if(isblank([completed date]),today(),[completed date])

Below part of formula means:

If the [completed date] is blank, then use today's date in date difference calculation.

If you only want to show "Pending Late" when [completed date] is blank, modify the formula as below:

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

Best Regards,

Jay