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

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