Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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."
but i can use is, just cannot measure it,
Help me please
Thank you
Best Regards
Solved! Go to Solution.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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
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"
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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.
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"
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
79 | |
73 | |
56 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |