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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculate no. of days in month filter

Dear All, 

I need a measure in which I want total number of days that I have selected in month filter.

For example - if I have selected Jan in filter then counts should be 31

If I selected Jan and Feb then counts should be 31+28 = 59

If I select April and May then counts should be 30+28[days as of today, not 31] = 58

 

Kindly let me know how to get it. Please help me in teh same. 

I am trying with below expression. it is giving me correct counts butu for current month, it is giving me 31 days but I want days as of today.

 

TotalNumberDaysMonth =
SUMX (
SUMMARIZE (
ADDCOLUMNS (
VALUES ( AD_V_AcreNext_Activity_Detail[Activity_Insert_At] ),
"EOMonth", EOMONTH (AD_V_AcreNext_Activity_Detail[Activity_Insert_At] , 0 )
),
[EOMonth]
),
DAY ( [EOMonth] )
)
1 ACCEPTED SOLUTION

Hi,

 

Please check my attached pbix file.

15.PNG

 

Best Regards,

Giotto

View solution in original post

10 REPLIES 10
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please take following steps:

1)Create a calendar table:

Calendar = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))

2)Create a month column as a slicer:

Month = FORMAT('Calendar'[Date],"MMM")

3)Try this measure:

Measure = COUNTROWS(FILTER('Calendar','Calendar'[Date]<=TODAY()))

4)When select Apr+May, the result shows(30+29(today)=59):

5.PNG

See my attached pbix file.

 

Best Regards,

Giotto

 

 

Anonymous
Not applicable

HI @v-gizhi-msft ,

I have another table Activity_Detail with datetime column.

If I join calander[date] with Activity_Detail[Activity_Date] column then date is not coming correctly in other pivots...

and in month slicer also month is not coming correctly.. instead of month date is coming -

 

I am trying it since couple of days but not happening by me but you guys can do it easily .. 😞

I think there is some issue in my model..

 

I need 3 things in my dashbaord -

1- Month and date slicer to be corrected - put it from calander 

2- Days wise Team Adoption - date is not correct as showing same date multiple times.. may be it is because of datetime

and date should be all dates of month(from calander )and then show activity counts for the available dates

3- Team Reporting - No. of Days :- I want to apply conditional formatting on on PO name . If 

[total working days]/[total no of days in selected month slicer] >90% then Green

[total working days]/[total no of days in selected month slicer] <90% then Red

 

 

Below is the PBIX -

https://drive.google.com/file/d/16JWMVy2MaPrGnDViCA36Un4eVaZ2lKTj/view?usp=sharing

 

Kindly help me in the same.

 

 

 

Anonymous
Not applicable

HI @v-gizhi-msft ,

I have another table Activity_Detail with datetime column.

If I join calander[date] with Activity_Detail[Activity_Date] column then date is not coming correctly in other pivots...

and in month slicer also month is not coming correctly.. instead of month date is coming -

 

I am trying it since couple of days but not happening by me but you guys can do it easily .. 😞

I think there is some issue in my model..

 

I need 3 things in my dashbaord -

1- Month and date slicer to be corrected - put it from calander 

2- Days wise Team Adoption - date is not correct as showing same date multiple times.. may be it is because of datetime

3- Team Reporting - No. of Days :- I want to apply conditional formatting on on PO name . If 

[total working days]/[total no of days in selected month slicer] >90% then Green

[total working days]/[total no of days in selected month slicer] <90% then Red

 

Below is the PBIX -

https://drive.google.com/file/d/16JWMVy2MaPrGnDViCA36Un4eVaZ2lKTj/view?usp=sharing

 

Kindly help me in the same.

 

 

 

amitchandak
Super User
Super User

@Anonymous , instead of eomonth try lastdate

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Dear @amitchandak ,

 

I am trying below measure but not working -

TotalNumberDaysMonth =
COUNT(Activity_Detail[Activity_Id])/
SUMX (
SUMMARIZE (
ADDCOLUMNS (
VALUES ( Activity_Detail[Activity_Insert_At] ),
"EOMonth",LASTDATE(Activity_Detail[Activity_Insert_At])
),
[EOMonth]
),
DAY ( [EOMonth] )
)
 
Could you please correct it, what is the issue?
 
also attached PBIX file .. KIndly have a look.. 
calander that was suggested by you.. not working properly.

@Anonymous , try if this can work

TotalNumberDaysMonth =
COUNT(Activity_Detail[Activity_Id])/
SUMX (
SUMMARIZE (Activity_Detail[Activity_Insert_At] ,"EOMonth"
,if(LASTDATE(Activity_Detail[Activity_Insert_At])<=today(),LASTDATE(Activity_Detail[Activity_Insert_At]),today())
),
DAY ( [EOMonth] )
)

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@amitchandak ,

It is giving me some error. May I send you the PBIX if you can check it .

erro.png

Anonymous
Not applicable

Below formula is giving me month days counts but only for one month.

If I am selecting 2 months and it is not summing up the days counts -

TotalNumberDaysMonth =
SUMX (
SUMMARIZE (
ADDCOLUMNS (
VALUES ( Activity_Detail[Activity_Insert_At] ),
"EOMonth", if(max(Activity_Detail[Activity_Insert_At])<=today(),max(Activity_Detail[Activity_Insert_At]),today())
),
[EOMonth]
),
DAY ( [EOMonth] )
)
Anonymous
Not applicable

I am getting total number of days but it is giving me as per the actvivity date available for employee. 

I have selected April and May in month filter then it is giving me 30 for some employees and 61 for some employee.

 

I need 61 for all employee so that I can calculate average working days .

 

Kindly help me.

erro.png

Hi,

 

Please check my attached pbix file.

15.PNG

 

Best Regards,

Giotto

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors