Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amirzoyan
Regular Visitor

Calculating Dates across Rows

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.example.png

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @amirzoyan,

 

I created a simple for test.

vcgaomsft_4-1648018944251.png

Please try.

Transform data.

vcgaomsft_1-1648017320706.png

Select all needed columns.

vcgaomsft_5-1648019108298.png

Unpivot Columns.

vcgaomsft_0-1648017237843.png

vcgaomsft_6-1648019153531.png

Select [Attribute] columns.

vcgaomsft_7-1648019233964.png

Pivot Column.

vcgaomsft_8-1648019303431.png

vcgaomsft_9-1648019368209.png

vcgaomsft_10-1648019428758.png

Close and Apply.

vcgaomsft_11-1648019477401.png

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

View solution in original post

6 REPLIES 6
v-cgao-msft
Community Support
Community Support

Hi @amirzoyan,

 

I created a simple for test.

vcgaomsft_4-1648018944251.png

Please try.

Transform data.

vcgaomsft_1-1648017320706.png

Select all needed columns.

vcgaomsft_5-1648019108298.png

Unpivot Columns.

vcgaomsft_0-1648017237843.png

vcgaomsft_6-1648019153531.png

Select [Attribute] columns.

vcgaomsft_7-1648019233964.png

Pivot Column.

vcgaomsft_8-1648019303431.png

vcgaomsft_9-1648019368209.png

vcgaomsft_10-1648019428758.png

Close and Apply.

vcgaomsft_11-1648019477401.png

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
Memorable Member
Memorable Member

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

In Query Editor (Power Query)
prateekraina
Memorable Member
Memorable Member

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

thanks for that!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.