First Week of Month in Power BI

0
votes

I have a date table with Day and Date columns in Power BI. I would like to add a column which tells me if that week includes the first day of month. For example when 1st of November is Friday, then it returns a "Yes" for that week. Therefore, the column "IsFirstWeekOfMonth" shows "Yes" for the working days of a week which has 1st day of month. Something like below:

enter image description here

Would you please let me know how I can do this in Power BI (M or DAX). Many thanks in advance

1
daxpowerbi-desktopm

1 Answers

1
votes

You can add a calculated column with the following logic, also checking if is or not a weekend day (for Saturdays and Sundays):

IsFistWeekOfMonth = IF(OR(WEEKDAY('Table'[Date].[Date],2)==6,WEEKDAY('Table'[Date].[Date],2)==7),"No",IF(AND(OR(WEEKNUM(DATE('Table'[Date].[Year],'Table'[Date].[MonthNo],1))==WEEKNUM('Table'[Date].[Date],2),WEEKNUM(DATE('Table'[Date].[Year],'Table'[Date].[MonthNo]+1,1))==WEEKNUM('Table'[Date].[Date],2)),WEEKDAY(DATE('Table'[Date].[Year],'Table'[Date].[MonthNo],1),2)IN{1,2,3,4,5}),"Yes","No"))

Please tell me if this can help you.