Graph of each day regarding a date range

[xdumont]

Hello,

I have a table with a capacity of kind of work per employee.

EmployeeName	KindOfWork	Capacity	FromDate	ToDate
Bob	Sales	0.8	01.01.2020	31.08.2020
Bob	Technical	0.2	01.01.2020	31.08.2020
Bob	Sales	0.9	01.09.2021	31.12.2021
Bob	Technical	0.1	01.09.2021	31.12.2021
Paul	Sales	1	01.01.2020	31.12.2021

I want to create a graph who show me the variation of the capacity month by month per KindOfWork.

How can I create this ? I need (I think), a table of each month (or day) between FromDate and ToDate, but I don't know how to create that.

Any idea? Thanks a lot for your help.

[v-rongtiep-msft · Accepted solution]

Hi @xdumont ,

Please refer to my pbix file to see if it helps you.

A table of each or day between FromDate and ToDate.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRCk7MSS0G0gZ6FkDSyMDIQN9Q3xDGtNA3NlSK1YEpDklNzsjLTE7MAWswIqwBYbolRIWhviVMMVCXET7jDfHqCEgszUGywBDDLXDFsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeName = _t, KindOfWork = _t, Capacity = _t, FromDate = _t, ToDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EmployeeName", type text}, {"KindOfWork", type text}, {"Capacity", type number}, {"FromDate", type date}, {"ToDate", type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"ToDate", type date}}),
    #"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type1", "Subtraction", each Duration.Days([ToDate] - [FromDate]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Date Subtraction", "Custom", each List.Dates([FromDate],[Subtraction],#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xdumont]

Hi @v-rongtiep-msft ,

Thanks a lot for your precious help, all work as wanted 😊