List.Dates between dates

[Anonymous]

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.

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.

[v-sihou-msft · Accepted solution]

@Anonymous 

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-power-query?forum=powerquery

Regards,