Unable to add WEEKNUM([date]) when I try to make week numbers to be under every month.

[Anonymous]

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")
        ))

Any help will be appreciated. 

Thanks in advance! 

[lbendlin · Accepted solution]

I think you inadvertently created a measure. Delete it and recreate as calculated column.

[Anonymous]

You're right. I was creating one with measure not column. 

Thanks!