Measure not displaying full amount

[Anonymous]

Hi,

I am trying to display the number of weekdays for each month but the selecting filter is not working

In the screenshot below, when I select Apr, it correctly shows the April numbers, but when not selecting any month, it does not show the sum of days from Jan-Jun

     

The measure I am using to find the "Count Weekday" is : 

Count Weekday Official = average(CP_Consolidated[DayCount])

The column formula I am using to find number of days in a month is :

DayCount =

VAR __monthstart =

EOMONTH ( CP_Consolidated[Date], -1 ) + 1

VAR __monthend =

EOMONTH ( CP_Consolidated[Date], 0 )

VAR __minthisemploye =

CALCULATE ( MIN ( CP_Consolidated[Date]), ALLEXCEPT ( CP_Consolidated,CP_Consolidated[Crew#] ) )

RETURN

IF (

ISBLANK (

CALCULATE (

COUNTROWS ( CP_Consolidated ),

ALLEXCEPT ( CP_Consolidated,CP_Consolidated[Crew#] ),

CP_Consolidated[Date] < __monthstart

)

),

DATEDIFF ( __minthisemploye, __monthend, DAY ),

DATEDIFF ( __monthstart, __monthend, DAY ) + 1

)

Is there a way so that when I do not select any month, the sum of days from Jan-Jun correctly are displayed?

Please let me know if this question does not make sense

Thank you!
Sarah

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could create a measure to get the slicer status. If the result returns blank, you could make it return the sum of all months.

Measure =
VAR a =
    SELECTEDVALUE ( 'Slicer'[Month] )
RETURN
    IF (
        ISBLANK ( a ),
        SUMX ( ALLSELECTED ( 'Table'[Month] ), [Count Weekday Official] ),
        [Count Weekday Official]
    )

[aj1973]

Hi @Anonymous 

In your date Table create a Column for the workdays 

WorkingDay_Mark =

VAR WeekDayNum =

WEEKDAY ( DimDate[Date] )

RETURN

(

IF ( WeekDayNum = 1 || WeekDayNum = 7 ,0,1)

)

the use a slicer for the dates and a Card for your weekdays

Regards

[Anonymous]

thank you, all!

but my main question is how to get the correct total number of days between jan and jun if no dates are selected in my filter

Thank you!
Sarah

[aj1973]

@Anonymous 

Change the date slicer to view it 'Between' or 'relative dates'

[amitchandak]

@Anonymous , you can create a workday column in date table and sum it up

Workday= if(WEEKDAY([Date],2)<6,1,0)

[nvprasad]

Hi,

Can you try below measure to calculate count of week days?

Weekday_Check =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( ALL ( 'Table'[Date] ), WEEKDAY ( 'Table'[Date], 2 ) < 6 )
)

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad