Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure not displaying full amount

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
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

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

     

6 Replies

  • nvprasad's avatar
    nvprasad
    Icon for Solution Sage rankSolution Sage
    6 years ago

    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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

     

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

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        aj1973
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous 

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

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    6 years ago

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