Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculate date difference between two columns

Hello,   I'm new to Power BI. There are two columns in Excel, each containing both dates and text. I want to import them into Power BI and only calculate the date difference of the rows containing...
General Comment
Need Help
  • DataNinja777's avatar
    DataNinja777
    2 years ago

    Hi Anonymous ,

     

    There are multiple ways to achive your required output even though the text descriptions have multiple variations.  My example below is based on the assumption that your text description variants do not contain "/" used in the date text. With this assumption the original calculated column can be modified as follows:

    Date Diff = 
VAR Date2 =
    IF (
        CONTAINSSTRING ( 'Table'[Date 2], "/" ) = TRUE,
        FORMAT ( 'Table'[Date 2], "m/d/yyyy" ),
        FORMAT ( 'Table'[Date 1], "m/d/yyyy" )
    )
RETURN
    IF ( Date2 - [Date 1] <> BLANK (), Date2 - [Date 1] )

     

    I attach a modified pbix file as an example. 

    Best regards,

DataNinja777's avatar
DataNinja777
Super User
2 years ago

Hi Anonymous ,

 

There are many ways to achive your required output and one of them is by writing a calculated column like below:

Date Diff = 
VAR Date2 =
    IF (
        'Table'[Date 2] = "pending",
        FORMAT ( 'Table'[Date 1], "m/d/yyyy" ),
        'Table'[Date 2]
    )
RETURN
    IF ( Date2 - [Date 1] <> BLANK (), Date2 - [Date 1] )

 

I attach an example pbix file.  

Best regards,

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi DataNinja777 ,

 

Thanks for your quick response.

Sorry for my previous unclear description. The text content in the Date 2 column might not only be "pending". It could also be a variety of other text contents. How should I write the expression?

  • DataNinja777's avatar
    DataNinja777
    Super User
    2 years ago

    Hi Anonymous ,

     

    There are multiple ways to achive your required output even though the text descriptions have multiple variations.  My example below is based on the assumption that your text description variants do not contain "/" used in the date text. With this assumption the original calculated column can be modified as follows:

    Date Diff = 
VAR Date2 =
    IF (
        CONTAINSSTRING ( 'Table'[Date 2], "/" ) = TRUE,
        FORMAT ( 'Table'[Date 2], "m/d/yyyy" ),
        FORMAT ( 'Table'[Date 1], "m/d/yyyy" )
    )
RETURN
    IF ( Date2 - [Date 1] <> BLANK (), Date2 - [Date 1] )

     

    I attach a modified pbix file as an example. 

    Best regards,