Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.