Forum Discussion
The value for 'when' cannot be determined. Either the column doesn't exist
- You will probably need to set up a DimDate table for this, which will have a column for "Week of Month" and 'Week of Year", then you can use AVERAGEX(Values(DimDate[WeekOfMonth]), SUM(Table[Attendance])) and put that in a visual with DimDate[Month]
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Hope that helps! You're welcome chu_en
To get the week to start on Sunday, add the optional argument to Week of month function:
https://docs.microsoft.com/en-us/powerquery-m/date-weekofmonth
Sunday corresponds to 0 day of the week, so:
Click the settings cog next to insert Week of Month column then update code to:
Date.WeekOfMonth([Date],0)
Or the entire code for table below:
let
startDate = #date(2019, 1, 1),
endDate = Date.From(DateTime.LocalNow()),
Dates = List.Dates(startDate, Duration.Days(endDate - startDate), #duration (1,0,0,0)),
#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted DateKey" = Table.AddColumn(#"Changed Type", "DateKey", each Date.ToText([Date],"yyyyMMdd"), type text),
#"Inserted Year" = Table.AddColumn(#"Inserted DateKey", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted FY Quarters" = Table.AddColumn(#"Inserted Quarter", "FY Quarter", each if [Quarter] = 1 then "Q4" else if [Quarter] = 2 then "Q1" else if [Quarter] = 3 then "Q2" else "Q3", type text),
#"Inserted Month Name" = Table.AddColumn(#"Inserted FY Quarters", "Month name", each Date.MonthName([Date]), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month number", each Date.Month([Date]), Int64.Type),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Month", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],0), Int64.Type),
#"Inserted Day of Year" = Table.AddColumn(#"Inserted Week of Month", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day of Year", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Inserted Day of Month" = Table.AddColumn(#"Inserted Day of Week", "Day of month", each Date.Day([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Month", "Day name", each Date.DayOfWeekName([Date]), type text),
#"Inserted FY start" = Table.AddColumn(#"Inserted Day Name", "FY starts", each [Year] + (if [Month number] > 3 then 0 else -1), type number),
#"Inserted FY" = Table.AddColumn(#"Inserted FY start", "FY", each Text.From([FY starts]) & "/" & Text.From([FY starts] + 1), type text)
in
#"Inserted FY"
Are you trying to average weeks/month across the year or the value in another column aggregated weekly?
what i'm trying to use formula is
sum(attendance) / count (week of month)
eg.
WHEN, ATTENDANCE
2020-10-04, 2
2020-10-11, 4
2020-10-18, 4
2020-10-25, 2
Total Oct is 12, and devide by total of week is 4 then Average of Oct is 3
and i dont know how to get total week of month, cause i'm creating measure but "when" cannot be determined.
Thank you AllisonKennedy
- You will probably need to set up a DimDate table for this, which will have a column for "Week of Month" and 'Week of Year", then you can use AVERAGEX(Values(DimDate[WeekOfMonth]), SUM(Table[Attendance])) and put that in a visual with DimDate[Month]
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Hope that helps!Wow, its worked, but one more question, how do i edit in the formula the week of the month, starting the first sunday of the month count as week 1
eg.
thank you AllisonKennedy
You're welcome chu_en
To get the week to start on Sunday, add the optional argument to Week of month function:
https://docs.microsoft.com/en-us/powerquery-m/date-weekofmonth
Sunday corresponds to 0 day of the week, so:
Click the settings cog next to insert Week of Month column then update code to:
Date.WeekOfMonth([Date],0)
Or the entire code for table below:
let
startDate = #date(2019, 1, 1),
endDate = Date.From(DateTime.LocalNow()),
Dates = List.Dates(startDate, Duration.Days(endDate - startDate), #duration (1,0,0,0)),
#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted DateKey" = Table.AddColumn(#"Changed Type", "DateKey", each Date.ToText([Date],"yyyyMMdd"), type text),
#"Inserted Year" = Table.AddColumn(#"Inserted DateKey", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted FY Quarters" = Table.AddColumn(#"Inserted Quarter", "FY Quarter", each if [Quarter] = 1 then "Q4" else if [Quarter] = 2 then "Q1" else if [Quarter] = 3 then "Q2" else "Q3", type text),
#"Inserted Month Name" = Table.AddColumn(#"Inserted FY Quarters", "Month name", each Date.MonthName([Date]), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month number", each Date.Month([Date]), Int64.Type),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Month", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],0), Int64.Type),
#"Inserted Day of Year" = Table.AddColumn(#"Inserted Week of Month", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day of Year", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Inserted Day of Month" = Table.AddColumn(#"Inserted Day of Week", "Day of month", each Date.Day([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Month", "Day name", each Date.DayOfWeekName([Date]), type text),
#"Inserted FY start" = Table.AddColumn(#"Inserted Day Name", "FY starts", each [Year] + (if [Month number] > 3 then 0 else -1), type number),
#"Inserted FY" = Table.AddColumn(#"Inserted FY start", "FY", each Text.From([FY starts]) & "/" & Text.From([FY starts] + 1), type text)
in
#"Inserted FY"