Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

 

14 REPLIES 14

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??

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Community Champion
Community Champion

@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]) 

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

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

 

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

Simple and it does the trick, thanks!

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

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:

8.PNG

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

@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

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:

8.PNG

Regrads

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

 

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

 

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.