Calculating Dates across Rows

[amirzoyan]

Hi, 

Dealing with somewhat of an odd problem. The example below, the data gets pulled automatically and the dates for a single report show up in different rows. 

I know how to calculate # of days when the dates are in the same row, but not clear how to do it across rows. Any suggestions? 

Including an image below - trying to figure out how to calculate # of days between the two circled dates.

[Anonymous · Accepted solution]

Hi @amirzoyan,

I created a simple for test.

Please try.

Transform data.

Select all needed columns.

Unpivot Columns.

Select [Attribute] columns.

Pivot Column.

Close and Apply.

Attach the PBIX file for reference. Hope it helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[prateekraina]

Please upvote my answer if it helped resolve your issue 🙂 Prateek Raina

[prateekraina]

In Query Editor (Power Query)

[prateekraina]

Hi, Here is a quick hack which will help you bring all the dates in 1 row and then you can easily find Date Difference. Step 1: Unpivot all the rows = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value") Step 2: Pivot the rows back. It will give you all the dates in single row. = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value") Hope it helps Prateek Raina

[amirzoyan]

thanks for that!

but just to confirm - the steps you listed, how and where do I accomplish this?

[AlexisOlson]

This documentation is pretty good:
https://docs.microsoft.com/en-us/power-query/unpivot-column