cancel
Showing results for
Did you mean:
Frequent Visitor

day of month in DAX( Power bi desktop)

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!.

10 REPLIES 10
Super Contributor

Re: day of month in DAX( Power bi desktop)

@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]) `
Frequent Visitor

Re: day of month in DAX( Power bi desktop)

@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.

Super Contributor

Re: day of month in DAX( Power bi desktop)

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

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

Frequent Visitor

Re: day of month in DAX( Power bi desktop)

@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.

Senior Member

Re: day of month in DAX( Power bi desktop)

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

/sdjensen
New Contributor

Re: day of month in DAX( Power bi desktop)

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

Frequent Visitor

Re: day of month in DAX( Power bi desktop)

@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

Best regards.

J

New Contributor

Re: day of month in DAX( Power bi desktop)

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:

Frequent Visitor

Re: day of month in DAX( Power bi desktop)

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

Announcements

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Get your latest community news and announcements.

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 208 members 1,970 guests
Recent signins: