How to get week number per month on Power Bi?

thechans7 0
Viewed 26

I have a a column called Date originated that hold the a date like "Tuesday, March 21, 2022". I want to get that week number per month but the following gives me incorrect numbers. The following DAX: Week of Month = 1 + WEEKNUM('ECR'[Date Originated]) - WEEKNUM(STARTOFMONTH('ECR'[Date Originated])) gives me 1 for that date. Some entries are correct like for Wednesday, April 6, 2022 I do get 1

dax powerbi
2 Answers

The problem is not the DAX expression, but the date column, which you didn't share with us.

userhiPt0

STARTOFMONTH is not the right choice of function here, since that function does not by default return the first date of a given month; rather, it returns the first date of the month for the supplied dates (within the current context).

Here you are supplying only one date, and so, for example:

=STARTOFMONTH(21/03/2022)

will simply return 21/03/2022.

You should be using

=
1 + WEEKNUM( 'ECR'[Date Originated] )
    - WEEKNUM( EOMONTH( 'ECR'[Date Originated], -1 ) + 1 )
Jos Woolley0
Related