Average Weekly Usage

[DanniiMarie]

I need to find the correct syntax for a Power Query formula that will calculate average weekly usage in my newly inserted custom column. Here's what I have in Excel:

September 2021	October 2021	November 2021	Average Weekly Usage
198	332	80	=(SUM(A2:C2)/(DAY(EOMONTH(A2,0))+DAY(EOMONTH(B2,0))+DAY(EOMONTH(C2,0))))*7

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tcn = Table.ColumnNames(Source),
    weeks = List.Sum(List.Transform(tcn, each Date.DaysInMonth(Date.FromText(_))))/7,
    Result = Table.AddColumn(Source, "Average Weekly Usage", each List.Sum(Record.ToList(_))/weeks)
in
    Result

Book1
	A	B	C	D	E	F	G	H	I
1	September 2021	October 2021	November 2021		September 2021	October 2021	November 2021	Average Weekly Usage
2	198	332	80		198	332	80	46.92
3
Sheet3

 

[DanniiMarie]

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tcn = Table.ColumnNames(Source),
    weeks = List.Sum(List.Transform(tcn, each Date.DaysInMonth(Date.FromText(_))))/7,
    Result = Table.AddColumn(Source, "Average Weekly Usage", each List.Sum(Record.ToList(_))/weeks)
in
    Result

Book1
	A	B	C	D	E	F	G	H	I
1	September 2021	October 2021	November 2021		September 2021	October 2021	November 2021	Average Weekly Usage
2	198	332	80		198	332	80	46.92
3
Sheet3

I'm getting an error. I believe this error due to the fact that your solution assumed I only had four columns in my table. Unfortunately, this is not the case. In an attempt to simplify things for the sake of this post, I left out the left most columns in my example above. (Guess I over simplified to my detriment.) I need the query to only use the last three columns of my spreadsheet. I'm also not sure if it makes a difference or not that the table with this source data in it was produced using Power Query.

 

[JGordon11]

If the table is in an excel workbook then change "Table2" below to your table name and try:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tcn = List.LastN(Table.ColumnNames(Source),3),
    weeks = List.Sum(List.Transform(tcn, each Date.DaysInMonth(Date.FromText(_))))/7,
    Result = Table.AddColumn(Source, "Average Weekly Usage", each List.Sum(Record.ToList(_))/weeks)
in
    Result

If the table only exists in PQ then the first step simplifies to Source = your PQ table name

 

[DanniiMarie]

If the table is in an excel workbook then change "Table2" below to your table name and try:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tcn = List.LastN(Table.ColumnNames(Source),3),
    weeks = List.Sum(List.Transform(tcn, each Date.DaysInMonth(Date.FromText(_))))/7,
    Result = Table.AddColumn(Source, "Average Weekly Usage", each List.Sum(Record.ToList(_))/weeks)
in
    Result

If the table only exists in PQ then the first step simplifies to Source = your PQ table name

 

[DanniiMarie]

If the table is in an excel workbook then change "Table2" below to your table name and try:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tcn = List.LastN(Table.ColumnNames(Source),3),
    weeks = List.Sum(List.Transform(tcn, each Date.DaysInMonth(Date.FromText(_))))/7,
    Result = Table.AddColumn(Source, "Average Weekly Usage", each List.Sum(Record.ToList(_))/weeks)
in
    Result

If the table only exists in PQ then the first step simplifies to Source = your PQ table name

I'm still getting an error down the column.

 

[JGordon11]

If you post a representative sample of your source table, I'll try to debug.

 

[DanniiMarie]

Ideally, I'd like a weekly average calculated in column F based on the number of weeks that are represented in the three month columns. Since the last month is not always a full month, the system would have to calculate how many weeks are between the first of the month in column E and the current date. As of the time that I am composing this message, the date is 12/7 so this calculation would have to know that only one full week has passed in December. This means that the column would calculate: ((198+332+80)/(31,30,7))*7 = 62.79 units per week on average.

 

[JGordon11]

Problem was I thought your column headers were the month names. Looks like your Column headers are Column1, Column2 ... Column21 and the month names are in the first row of the table. Is that the way you have it set up?

 

[DanniiMarie]

Problem was I thought your column headers were the month names. Looks like your Column headers are Column1, Column2 ... Column21 and the month names are in the first row of the table. Is that the way you have it set up?

In the Source data, the column headers ARE the month names. Is there a way to convert the column headers to match the source data as they will change depending on the date in which the report is pulled?