Show contract hours per date range in model as weekly contract hours in visual

[genevievekoens]

I hope you all can help me. I have a data model where I have date ranges and corresponding contract hours. These can change several times a year. 

Now I have to display these contract hours on a weekly basis in my visual so we can look for differences in actual booked hours.

This is how the data looks:

This is an example of one person whose contract (Rooster) hours changed mid year.

This is how it looks in my visual:

I have a link on StartDate and now for 2022 it will only show me the hours on the date the change took effect in 2022. 

What I need is to show 27 for every week up until the new startDate and 32 for every week up until his Enddate.

Is this possible? And how?

Thank you in advance 🙂

[v-yalanwu-msft]

Hi, @genevievekoens ;

Is your problem solved?  If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi， @genevievekoens ；

Try it in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ11jVR0gEyjYx0TXSNzJVidaJhPEuIBFCFrqGhUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each let _a= [StartDate]
in 


List.Select( #"Changed Type"[StartDate],each  _>_a)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each {[EndDate]}),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each List.Zip({[Custom.1],[Custom.2]})),
    #"Expanded Custom.3" = Table.ExpandListColumn(#"Added Custom3", "Custom.3"),
    #"Added Custom4" = Table.AddColumn(#"Expanded Custom.3", "Custom.4", each List.Min([Custom.3])),
    #"Added Custom" = Table.AddColumn(#"Added Custom4", "Custom", each let a=[Custom.4]
in
List.Select(List.Dates([StartDate] ,99999999999,
#duration(1,0,0,0)), each   _< a)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom")
in
    #"Expanded Custom"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.