Calculate no. of days in month filter

[Anonymous]

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

)

[v-gizhi-msft]

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):

See my attached pbix file.

Best Regards,

Giotto

[Anonymous]

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]

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]

@Anonymous , instead of eomonth try lastdate

[Anonymous]

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.

[amitchandak]

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

[Anonymous]

@amitchandak ,

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

[Anonymous]

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]

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.

[v-gizhi-msft · Accepted solution]

Hi,

Please check my attached pbix file.

Best Regards,

Giotto