Center Utilization Per Month 445 period

[Anonymous]

Hello

Below is the table I am trying to calculate the weekly center utilisation summed monthly. And the PIBX at the bottom.

Centre Name	Manager	Daily Capacity	Week 30/12/19	Week 6/01/20	Week 13/01/20	Week 20/01/20
Centre 1	David	90	116	200	300	279
Centre 2	David	110	202	307	400	300
Centre 3	James	80	110	244	250	280
Centre 4	Rachel	72	109	110	131	121

So for Center 1 total capacity for this peroid is calcuated like this 90 * 5 days per week * 4 weeks in the peroid = 1800 

If you calculate the total utilisation over the 4 weeks this = 895

So my monthly utilization for Centre 1 is 49.72%

I need the center utilization on a monthly utilization percentage. 

This would be pretty simple execpt that I am only showing you data for the first peroid which is a 4 week peroid. The next peroid is another 4 weeks then this moves to a 5 week peroid. This is common for retail.

Can someone please help me with this Dax challenge. 

I do have a data table that I can use to reference but I was hoping to use the row dates if possible.

Thank you

You can download the data here

[Smauro]

Hi @Anonymous,

As @RobbeVL mentioned, you do need some serious remodeling in power query in order to make this data usable. For example, you'll need a table to distinguish your periods, and a table to actually have your data. Then you can do some dax calculations.

In your case, both seem to come from the same excel file, so, try these two:

Dates:

let
    Source = Excel.Workbook(File.Contents("C:\PATH\FILE.xlsx"), null, true),
    #"Attended Week_Sheet" = Source{[Item="Attended Week",Kind="Sheet"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(#"Attended Week_Sheet",{"Column1", "Column2", "Column3"}),
    OnlyDates = Table.RemoveRows(Table.FirstN(#"Removed Columns", 3), 1),
    #"Transposed Table" = Table.Transpose(OnlyDates),
    #"Changed Type" = Table.TransformColumnTypes(#"Transposed Table",{{"Column1", Int64.Type}, {"Column2", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "WeeksInPeriod"}, {"Column2", "PeriodStart"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"PeriodStart", Order.Ascending}}),
    Buffer = Table.Buffer(#"Sorted Rows"),

    RowCount = Table.RowCount(Buffer),
    KeepRows = let Rows = (t as table, l as list, k as number, n as number) =>
                if k < n then
                    let
                        tnew = Table.RemoveRows(t, k+1, l{0}-1),
                        knew = k + 1,
                        nnew = n - l{0} + 1,
                        lnew = let r = l{0} in List.RemoveFirstN(l, r),
                        Rest = @Rows(tnew, lnew, knew, nnew)
                    in
                        Rest
                else t
                in
                    Rows(Buffer, Buffer[WeeksInPeriod], 0, RowCount),
    #"Inserted PeriodEnd" = Table.AddColumn(KeepRows, "PeriodEnd", each Date.AddDays(Date.AddWeeks([PeriodStart], [WeeksInPeriod]), -1), type date)
in
    #"Inserted PeriodEnd"

Data:

let
    Source = Excel.Workbook(File.Contents("C:\PATH\FILE.xlsx"), null, true),
    #"Attended Week_Sheet" = Source{[Item="Attended Week",Kind="Sheet"]}[Data],
    OnlyData = Table.RemoveRows(Table.RemoveFirstN(#"Attended Week_Sheet", 2), 1),
    #"Promoted Headers" = Table.PromoteHeaders(OnlyData, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Week beginning", "Center"}, {"", "User"}, {"_1", "Daily Capacity"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Daily Capacity", "User", "Center"}, "Week", "Usage"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Week", type date}, {"Center", type text}, {"User", type text}, {"Daily Capacity", Int64.Type}, {"Usage", Int64.Type}})
in
    #"Changed Type"

Cheers,

smauro

[Anonymous]

Thank you Smauro

The PIBX has the peroids needed. There are two data sources. Both different Excel Sheets.

I attempted to add your code into the advanced editor and this seems to have filtered the Attended Weeks correctly. However the second code applied aginast the data table "Table1" returns 

"Expression.Error: The column '' of the table wasn't found."
"Details:" 

Can you please shed some light in this issue?

[RobbeVL]

Hi,

You will first need to remodel your data in order to perform usefull calculations.

I've performed this is the example file here.

How do you identify this period? Your examle file contained multiple periods together, this is 4-4-5 ? 

Robbe

[Anonymous]

Hello RobbeVL

I downloaded your sample thank you. 

The pibx I shared has the table relation for the dates using "Calendar WeekNumber". What I was trying to do was to sum the weeks aggregated by month ussing the 445 accounting peroids.