day of month in DAX( Power bi desktop)

[pvtrinh89]

Hi everyone,

I am finding the syntax for the day of month(

e.i :

the day of month for Jan =31

the day of month for Fer =29

the day of month for Mar =31

....

Could anyone give me the DAX to get it in power bi desktop?

Thank you so much!.

[Anupa_Jayakody]

Hi 

I used the below formula to count the days in a month, but it generates 29 as a result for certain months where as it should be 30/31

DynamicDaysOfMonth = DATEDIFF ( MIN ( 'Dates'[Date] ), ENDOFMONTH ( 'Dates'[Date] ), DAY )

Any idea why is it happening??

[Ashish_Mathur]

Hi,

Are you writing a calculated column formula or a measure?  Are you selecting a date in a slicer/filter.  Share some data and show the expected result.

[Anupa_Jayakody]

Hi Ashish

I am trying a measure, what i want to do is to get the result of the number of days of the selected month, i tried this with a +1 and it worked. 

[ankitpatira]

@pvtrinh89 not entirely sure if i unerstood you but if you're after a DAX function to retrieve day number from a date then it is as below.

=DAY([YourDateField]) 

[pvtrinh89]

@ankitpatira I mean the number day, not day number! this column counts every day in a month.

E.i

Jan has 31 days --> 31

Fer has 29 days -->29

ect...

best regards,

J.

[ankitpatira]

@pvtrinh89 yep i get you. use below DAX to create calculated column

= DAY(EOMONTH(YOURTABLE[DATECOLUMN],0))

[JOHNPINTOC]

Simple and it does the trick, thanks!

[pvtrinh89]

@ankitpatira  I forget a criteria for this, i need to count only days that workingday =Y. Please give me your advice on this.

btw, thank you so much!

Best regards,
J.

[v-micsh-msft]

Pvtrinh89,

We could count the workday in the following way:

First we need a date table (calendar table), we could create the date table(New table under Modeling) using the following formula:

CalendarDate = CALENDAR(

                             MIN(Usertable[date]),

                             max(Usertable[date])

                                  )

Add the Year and month column with the following formula:

Yearn = YEAR(CalendarDate[Date])

Monthnum = MONTH(CalendarDate[Date])

Then we need to add a calculated column to mark the workday, click New column under Modeling tab in Power BI desktop, enter the following formula:

isworkday = SWITCH(WEEKDAY(CalendarDate[Date]),1,0,7,0,1)

Once done, create a Measure to count the workday using the formula below:

Workdaynum = COUNTROWS(
                          FILTER(CalendarDate,
                                AND(CalendarDate[Date]>=FIRSTDATE(CalendarDate[Date]),
                                      AND(CalendarDate[Date]<=LASTDATE(CalendarDate[Date]),
                                       CalendarDate[isworkday]=1)
                                      )
                                  )
                             )

See the result:

Please note: Using calendar function, especially for the start time and the end time, as they are the time boundary, so when counting the workday, the workday number of beginning month and the ended month might be smaller than a full month. If full month is expected, change the Calendar formula with the following one:

CalendarDate = CALENDAR(

                                 EOMONTH(MIN(Usertable[Startdate]),-1)+1,

                                 EOMONTH(max(Usertable[Enddate]), 0)

                                      )

If any further help needed, please feel free to post back.

Regards

[pvtrinh89]

@v-micsh-msft Thank you so much! I would ask u a question about working day, because we have some special dates to enable workingday="N", for example:in the establish month of my company(April), all employees will have 3 first days of month to vacation, so that days have workingdate = "N"... 

Secondly, I want to a new column to count the working day for each day in a month, i.e:   
1 Apr  ---> count(workingday)=1 

2 Apr  --->count(workingday)=2

...

30 Apr  ---> count(workingday0=22

Thank in advanced

Best regards.

J

[v-micsh-msft]

Hi pvtrinh89,

So holiday should be marked as another type in column workdaytype ( the isworkday column)?

Currently I didn't have any good idea to filter those special days out. We may consider to manually remove those special days within the workdaytype column formula.

For the counted workday, we could create a column with the following formula:

WorkdayCount = SUMX(

                                FILTER( DateTable,

                                             EARLIER( DateTable[Month] ) = DateTable[Month] &&

                                             EARLIER( DateTable[Year] ) = DateTable[Year] &&
                                             EARLIER( DateTable[Day] ) >= DateTable[Day] ),

                                DateTable[isworkday] )

See the result:

Regrads

[prakashacharya]

Can you please post formula to calculate isWorkDay? I would like to have a table with holidays and ofcourse not count weekends.

[prakashacharya]

Can you please post formula to calculate isWorkDay? I would like to have a table with holidays and ofcourse not count weekends.

[sdjensen]

From what you write it sounds to me that what you actually want to do is to calculate the number of working days in a given month.

You should be able to do this simply by creating a measure counting all the rows in your calendar table where workingdays = Y