Gathering Hours Monthly, Need Them at Daily Level for trending

[Anonymous]

 

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. 

[amitchandak]

@Anonymous , 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

[Anonymous]

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

[Anonymous · Accepted solution]

@amitchandak Thanks for your contribution on this thread.

Hi @Anonymous ,

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"

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