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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Stambla
Frequent Visitor

Gathering Hours Monthly, Need Them at Daily Level for trending

Tables.png

Problem: I am gathering hours by site on a monthly basis. I need to take the monthly hours and break them out to hours per a day.  Once we get hours per a day, I will use formula to calculate rates. The rates need to be able to be trended on the site level.  Current method results in jerky trends on line graph, I want it to smooth out. 

 

 

1 ACCEPTED SOLUTION

@amitchandak Thanks for your contribution on this thread.

Hi @Stambla ,

I create a sample pbix file(see the attachment) for you, please check if that is what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dczBDcAgDEPRXXJGwnEKJLMg9l+jVBWnpicfnvznFK1aCZoU8Us99qoBkFVSJKyPBAPPE67tF3my/GYNMTJ8n8G+s+sG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, SiteID = _t, Estimated_WorkHours = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"SiteID", Int64.Type}, {"Estimated_WorkHours", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Month", "SiteID"}, {{"Estimated_WorkHours", each List.Sum([Estimated_WorkHours]), type nullable number}}),
 #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Date", each List.Dates([Month], Number.From(Date.EndOfMonth([Month])-[Month])+1, #duration(1, 0, 0, 0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Daily Hours", each [Estimated_WorkHours]/Duration.Days(Date.EndOfMonth([Month])-[Month])+1)
in
    #"Added Custom1"

vyiruanmsft_0-1716193120140.png

In addition, you can refer the following links to get it by DAX.

Solved: Split monthly values to daily values - Microsoft Fabric Community

sql server - Calculate daily targets based on monthly targets Sales Power bi - Stack Overflow

Best Regards

Community Support Team _ Rena
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

3 REPLIES 3
amitchandak
Super User
Super User

@Stambla , refer

 

 

Power BI Dax Measure- Allocate data between Range- https://youtu.be/O653vwLTUzM

 

Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

 

Using month start and end date

Power BI - Get Dates between range using Power Query: https://youtu.be/SFEPsKfqIks

Good start, but mind helping me write the dax query? 3.5 hour video is the first link.. 

@amitchandak Thanks for your contribution on this thread.

Hi @Stambla ,

I create a sample pbix file(see the attachment) for you, please check if that is what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dczBDcAgDEPRXXJGwnEKJLMg9l+jVBWnpicfnvznFK1aCZoU8Us99qoBkFVSJKyPBAPPE67tF3my/GYNMTJ8n8G+s+sG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, SiteID = _t, Estimated_WorkHours = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"SiteID", Int64.Type}, {"Estimated_WorkHours", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Month", "SiteID"}, {{"Estimated_WorkHours", each List.Sum([Estimated_WorkHours]), type nullable number}}),
 #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Date", each List.Dates([Month], Number.From(Date.EndOfMonth([Month])-[Month])+1, #duration(1, 0, 0, 0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Daily Hours", each [Estimated_WorkHours]/Duration.Days(Date.EndOfMonth([Month])-[Month])+1)
in
    #"Added Custom1"

vyiruanmsft_0-1716193120140.png

In addition, you can refer the following links to get it by DAX.

Solved: Split monthly values to daily values - Microsoft Fabric Community

sql server - Calculate daily targets based on monthly targets Sales Power bi - Stack Overflow

Best Regards

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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