cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
xdumont
Regular Visitor

Graph of each day regarding a date range

Hello,

 

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

EmployeeNameKindOfWorkCapacityFromDateToDate
BobSales0.801.01.202031.08.2020
BobTechnical0.201.01.202031.08.2020
BobSales0.901.09.202131.12.2021
BobTechnical0.101.09.202131.12.2021
PaulSales101.01.202031.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.

1 ACCEPTED SOLUTION
v-rongtiep-msft
Community Support
Community Support

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"

vpollymsft_0-1670476757674.png

 

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.

View solution in original post

2 REPLIES 2
v-rongtiep-msft
Community Support
Community Support

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"

vpollymsft_0-1670476757674.png

 

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.

Hi @v-rongtiep-msft ,

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors