DAX HELP - Instances of Days in Month

[Anonymous]

Hi,

I need to Indentify the daay and the instance number of the day within the month...preferably in a calculated column.

For example 06/03/2017 is a monday and that would be instance  = 1 13/03/2017 would be Instance =  2

How would I achieve this with dax and calculated column.

Thanks

C

[v-huizhn-msft]

Hi @Anonymous,

Based on my understanding, you consider the first monday of each month as instance 1, the second monday as instance 2, until there is Monday, right? If it is, you can follow the steps below.

1.I create a sample table named Date, which including date from 2017/3/1 to 2017/4/30.

2.Create calculated column to get month and weekday using the formulas.

Month = MONTH('Date'[Date])
Weekday = WEEKDAY('Date'[Date],2)

3. Create a calculated column to get the instace number, please review the following formula and expected result shown in screenshot.

instance number = IF('Date'[Weekday]=1,RANKX(FILTER('Date','Date'[Weekday]=1&&'Date'[Month]=EARLIER('Date'[Month])),'Date'[Date],,ASC),BLANK())

If this is not what you want, please share more details and sample table for further analysis.

Best Regards,
Angelia

[Anonymous]

Hi @v-huizhn-msft Angelia,

Thanks for response.  I ran your code but it seems to be outstepping the month and continuing to count up instead of resetting for the next month.

instance number = IF('Calendar$'[Weekday]=1,RANKX(FILTER('Calendar$','Calendar$'[Weekday]=1&&'Calendar$'[Month]=EARLIER('Calendar$'[Month])),'Calendar$'[Date],,ASC),BLANK())

Example

March 6  = 1

March 13 = 2

March 20 = 3

March 27  = 4

April 3 = 1

I will also need to do the same for all weekdays.

I have the same table structure as you

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

In your fact table, there are different years, please create a calculated column using the formula, and check if it works fine.

instance number = IF('Calendar$'[Weekday]=1,RANKX(FILTER('Calendar$', 'Calendar$'[Weekday]=1&&'Calendar$'[Month]=EARLIER('Calendar$'[Month])&&'Calendar$'[Year]=EARLIER('Calendar$'[Year])),'Calendar$'[Date],,ASC),BLANK())

Best Regards,
Angelia