Table with Months as columns - but have Start Date and Finish Date (date range)

[heyheyhey]

Hello,

I have a table of requests...

Requestor Department Start Date Finish Date

Joe Bloggs	HR	03/10/2016	06/10/2016
John Smith	Marketing	13/11/2016	13/11/2016
Sam Boon	Finance	27/10/2016	07/11/2016
Joe Bloggs	HR	19/10/2016	27/10/2016

That I want to summarise in a table in Power BI as something like below (numbers are days). Basically I want it to calculate the duration between each pair of dates, within each month -- excluding weekends.

Requestor Department September October November

Joe Bloggs	HR		11
John Smith	Marketing		3	5
Sam Boon	Finance	7

Some of the date ranges span multiple months e.g. with Sam, it would need to work out that of the date range (27/10/2016 - 07/11/2016), 3 days are in October and 5 days are in November. 

Is something like the above table possible with Power BI??

Many thanks 🙂

[ImkeF]

You can do this in the query editor like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlVwyslPTy9W0lHyCAISBsZ6hgZ6RgaGZiCOGZwTqxOt5JWfkacQnJtZkgGU800syk4tycxLB7INgZoMYZqQOCBNwYm5Ck75+XlAGbfMvMS85FQgy8gc2RZzFA1e+ehuMrREUo2kNTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Department = _t, Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Department", type text}, {"Start", type date}, {"End", type date}}),
    AddListOfAllDays = Table.AddColumn(#"Changed Type", "AllDays", each {Number.From([Start])..Number.From([End])}),
    #"Expanded AllDays" = Table.ExpandListColumn(AddListOfAllDays, "AllDays"),
    ChangeToDateFormat = Table.TransformColumnTypes(#"Expanded AllDays",{{"AllDays", type date}}),
    AddDayOfWeek = Table.AddColumn(ChangeToDateFormat, "DayOfWeek", each Date.DayOfWeek([AllDays])),
    FilterOutWeekends = Table.SelectRows(AddDayOfWeek, each ([DayOfWeek] <> 5 and [DayOfWeek] <> 6)),
    CalculateMonth = Table.AddColumn(FilterOutWeekends, "Month", each Date.Month([AllDays])),
    #"Removed Columns" = Table.RemoveColumns(CalculateMonth,{"AllDays"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Month", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Month", type text}}, "de-DE")[Month]), "Month", "DayOfWeek", List.Count)
in
    #"Pivoted Column"

Please let me know if you need help implementing this.

[heyheyhey]

Hi ImkeF,

Thanks for your reply! I slightly simplifed the table in my example (it's from a SQL table); I tried to do just the first step (add the custom column AllDays, expand it, and then Close & Apply) and it gave me the following error:

Many thanks

[ImkeF]

Could you please post your query-code from the advanced editor?

Thanks!