WeekNum fails with week starting on Monday, but not Sunday

[Hougaard77]

Hi,

I have a calculated column in my report called ReportWeek.

It is defined as ReportWeek = WEEKNUM(NewData[ReportTime];1)

That works great, but if I change the number 1 to 2 like this, it fails:

ReportWeek = WEEKNUM(NewData[ReportTime];2)

The error is: Function 'WEEKNUM' is not allowed as part of calculated column DAX expressions on DirectQuery models.

How does that make sense and what can I do to make it work?

Best regards,

Erik

[v-haibl-msft · Accepted solution]

@Hougaard77

I’ve got response from the Product Team.

In DirectQuery mode, DAX formulas of calculated columns are limited to those which can be translated to SQL in an efficient matter. While it is possible to do so for WEEKNUM when return_type is set to 1, we haven't found an equivalent translation when return_type is 2. So this is a by design behavior of the product.

Best Regards,
Herbert

[v-huizhn-msft]

Hi @Hougaard77,

I can reproduce your scenario and I have reported internally. I will post the update if there is any response.

Best Regards,
Angelia