cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chu_en
Frequent Visitor

The value for 'when' cannot be determined. Either the column doesn't exist

Dear Community,

 

I have a date fields called "When", and i wanna count week from month to get average

and when i'm create new measure from it then the error show "The value for 'when' cannot be determined. Either the column doesn't exist, or there is no current row for this column."

 

Screenshot 2020-10-10 094705.pngbut i can use is, just cannot measure it,

 

Help me please

 

Thank you

Best Regards

2 ACCEPTED SOLUTIONS

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!

Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

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"


Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

5 REPLIES 5
AllisonKennedy
Super User
Super User

We need more info to help you please - what formula are you trying to use for DAX Measure and what other fields are you trying to use in the visual where you are seeing the error? How are these fields/tables related to 'when'? 



Are you trying to average weeks/month across the year or the value in another column aggregated weekly?

Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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!

Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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.

Screenshot 2020-10-10 122157.png

 

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"


Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors