Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!