1
votes

I am building a calculated column in PowerBI to identify the Month Number.

For Example Jan 2017 will be 1 and Jan 2018 will be 13 and not 1.

To do this, I usually do a calculated column like this :-

MonthNumber = DATEDIFF(Min(Orders[Date]),Orders[Date],MONTH) 

But Min function is not supported for Direct Query models. Is there a work around to get the same output in Direct Query.

1

1 Answers

1
votes

According to Microsoft's documentation, the MIN function should work using Direct Query.

However, if you scroll down to Modeling limitations on this page, you'll find the following limitation:

Limitations in calculated columns: Calculated columns are limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions.

The only workaround that occurs to me at the moment would be to hard-code the start date:

MonthNumber = DATEDIFF(DATE(2017, 1, 1), Orders[Date], MONTH)