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

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. 

      • 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