cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

List.Dates between dates

Hi All

 

I've got a problem I need help with.

 

I'm trying to plot on a bar chart months/yr on the horizontal axis and value on the Y. I have a project table that has start and end dates on it. I have another table with contractors on it and all the projects they're working on. I'm essentially trying to use their turnover, for example 3 million, and divide that by the number of months the projects duration is for. Then I'll plot that value on the months they're working on the project on the bar chart to give an idea of capacity.

 

FormatProblemDateFormatDuration.PNG

 

I think I'm nearly there. I've tried to get my table to list all the dates between the two dates as you can see in my formula. Then I'll convert those dates into Months and Years.

 

= Table.AddColumn(#"Changed Type1", "MonthYr", each List.Dates([Project Summary.Starting Date],Duration.Days(if [Project Summary.Practical Completion Date] = null then [Project Summary.Ending Date] else [Project Summary.Practical Completion Date]-[Project Summary.Starting Date])+1,#duration(1,0,0,0)))

 

I'm getting a format issue here I can't figure our why it's going wrong. I do accept that where the completion date is null I'll get it from the Ending Date instead.

1 ACCEPTED SOLUTION
Microsoft
Microsoft

 

@martinmcmillz 

 

As error messaged said, you must keep a "Duration" type data within Duration.Days() function. Based on your logic, when [Project Summary.Practical Completion Date] is null, it will return [Project Summary.Ending Date] which is Date type. 

 

In your scenario, I think you should use Duration.From() in your Duration.Days() part. 

 

Duration.Days(Duration.From(if [Project Summary.Practical Completion Date] = null then [Project Summary.Ending Date]-[Project Summary.Starting Date] else [Project Summary.Practical Completion Date]-[Project Summary.Starting Date]))+1

 

Please refer to a slimilar thread: 

 

https://social.technet.microsoft.com/Forums/en-US/af9dbbf7-2f13-41f3-9a51-7c5d6db1ee9d/datediff-in-p...

 

Regards,

View solution in original post

1 REPLY 1
Microsoft
Microsoft

 

@martinmcmillz 

 

As error messaged said, you must keep a "Duration" type data within Duration.Days() function. Based on your logic, when [Project Summary.Practical Completion Date] is null, it will return [Project Summary.Ending Date] which is Date type. 

 

In your scenario, I think you should use Duration.From() in your Duration.Days() part. 

 

Duration.Days(Duration.From(if [Project Summary.Practical Completion Date] = null then [Project Summary.Ending Date]-[Project Summary.Starting Date] else [Project Summary.Practical Completion Date]-[Project Summary.Starting Date]))+1

 

Please refer to a slimilar thread: 

 

https://social.technet.microsoft.com/Forums/en-US/af9dbbf7-2f13-41f3-9a51-7c5d6db1ee9d/datediff-in-p...

 

Regards,

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors