Distribute amount over working days based on start end date

[BobKoenen]

HI all, 

I want to calculate the amount of leave per working day for capacity insights. 

My data looks like this

Name	Start date	End date	Hours	Type of leave
Joe	1-1-2021	4-1-2021	16	Holliday
Jill	4-1-2020	7-1-2021	40	Parent leave
Body	5-1-2020	7-1-2021	32	Overtime comp.

I want my measure to look like this

Date	Measure sum of leave
1-1-2021	8
2-1-2021	0
3-1-2021	0
4-1-2021	8
5-1-2021	16
6-1-2021	16
7-1-2021	16

Would i need to add a working day column in my date table?

Hope you can help me

[selimovd]

Hey @BobKoenen ,

I think the easiest approach is in Power Query to expand the date range to all of the dates.

Then you can just do a COUNTROWS and it will show you the correct amount per day.

Try the following M-Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVdJRMtQ11DUyMDIEMk0QTEMzIOGRn5OTmZJYqRSrA1SdmZODUGMAZJoj6QTxAxKLUvNKFHJSE8tSwVqc8lMqgeKmWLUYGwEJ/7LUopLM3FSF5PzcAj2l2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Start date" = _t, #"End date" = _t, Hours = _t, #"Type of leave" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start date", type date}, {"End date", type date}, {"Hours", Int64.Type}, {"Type of leave", type text}}),
    #"Changed Date to Number" = Table.TransformColumnTypes(#"Changed Type",{{"Start date", Int64.Type}, {"End date", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Date to Number", "DateRange", each {[Start date] .. [End date]}),
    #"Expanded DateRange" = Table.ExpandListColumn(#"Added Custom", "DateRange"),
    #"Changed back to date" = Table.TransformColumnTypes(#"Expanded DateRange",{{"Start date", type date}, {"End date", type date}, {"DateRange", type date}})
in
    #"Changed back to date"

The result will look like this:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[BobKoenen]

HI DenSel,

Thankx but this does not give me a tabel with only values on working days. I think this gets my only halfway

[BobKoenen · Accepted solution]

sorry figured it out already