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

dineshj23's avatar
dineshj23
Helper I
3 years ago

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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