Calculate date difference between two columns

[JovieZ]

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 dates and I need to display the date difference in the report. How should I do this? 

Thanks.

For example, for the two rows in the following screenshot, I only need to calculate the date difference of the second row (5/12/2024-5/8/2024). 

[DataNinja777]

Hi @JovieZ ,

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,

[JovieZ]

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 · Accepted solution]

Hi @JovieZ ,

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,

[foodd]

Hello @JovieZ, and thank you for sharing your question with other members of the Community Forum who are supportive.   Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!