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]

If your months are type text this should work. If they are type date or type number then it won't.

let
    tbl = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source = Table.PromoteHeaders(tbl),
    months = List.LastN(Table.ColumnNames(Source),3),
    fullmonthweeks = List.Sum(List.Transform(months, each Date.DaysInMonth(Date.FromText(_))))/7,
    dt = DateTime.LocalNow(),
    weeks = if Date.FromText(months{2}) = #date(Date.Year(dt),Date.Month(dt),1) then 
            fullmonthweeks - Duration.Days(Date.EndOfMonth(dt) - dt)/7 else fullmonthweeks,
    Result = Table.AddColumn(Source, "Average Weekly Usage", each Number.Round(List.Sum(List.LastN(Record.ToList(_),3))/weeks,2))
in
    Result