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-polly-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-polly-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-polly-msft ,

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.