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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.