The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I'm trying to get Week numbers by Month, so it goes,
Jan - 1,2,3,4
Feb - 1,2,3,4
Mar - 1,2,3,4,5
like this.
I found a DAX from https://community.powerbi.com/t5/Desktop/How-to-show-week-number-per-month/td-p/83607 and tried @v-micsh-msft 's solution but it doesn't like [date] value in WEEKNUM() in the measure.
I can see the error says it happens when the formula refers to a column that contains many values without specifying an aggregation such as min, max, ect, which I have in my DateKey table.
Can anyone tell me how I can fix it to use Week number by month?
<'DateKey' table>
DateKey =
VAR BaseCalendar = CALENDAR(min('All Tickets'[Created Date Time]), max('All Tickets'[Created Date Time]))
RETURN
GENERATE (
BaseCalendar,
VAR BaseDate = [Date]
VAR YearDate = YEAR ( BaseDate )
// VAR WeekNum = 1 + WEEKNUM(BaseCalendar) - WEEKNUM(STARTOFMONTH('All Tickets'[Created Date]))
VAR MonthNumber = MONTH ( BaseDate )
VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1
vAR YearWeek = WEEKDAY( BaseDate )
RETURN ROW (
"Day", BaseDate,
"Year", YearDate,
"Month Number", MonthNumber,
"Month", FORMAT ( BaseDate, "mmmm" ),
"Month Short", FORMAT( BaseDate, "mmm"),
"Year Month Number", YearMonthNumber,
"Year Month", FORMAT ( BaseDate, "mmm yy" ),
"Year Month Short", FORMAT( BaseDate, "yyyy/mmm" ),
"Week", FORMAT( BaseDate, "dddd"),
"Week Short", FORMAT( BaseDate, "ddd"),
"Week Number", WEEKNUM(BaseDate),
"Quarter", "Q" & FORMAT( BaseDate, "Q"),
"Year Quarter", FORMAT( BaseDate, "yyyy") & "/Q" & FORMAT( BaseDate, "Q"),
"Datekey", FORMAT(BaseDate, "yyyymmdd")
))
Solved! Go to Solution.
I think you inadvertently created a measure. Delete it and recreate as calculated column.
I think you inadvertently created a measure. Delete it and recreate as calculated column.
You're right. I was creating one with measure not column.
Thanks!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |