Forum Discussion

Roach_1337's avatar
Roach_1337
Frequent Visitor
3 years ago
Solved

Sum by month?

Hello,

 

I have a report which needs to calculate the number of business days per month.

 

Right now there is a table that has a Day_Count column where 1= business day and 0 = weekend/holiday.

 

I need to have a field which just has the total # of business days per month.

 

if I put Sum of Day_Count into a visual I can see the correct # of days

I just need to turn this number (21) is to a usable field.

 

Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Roach_1337 ,

     

    I think you want to use the value of count business day.

    I think you can try below code to create a measure or calculated column. You need to add a Year and Month column in your Date table to help calculation.

    Count Business Day =
CALCULATE (
    COUNT ( 'Date'[Date] ),
    FILTER (
        ALLEXCEPT ( 'Date', 'Date'[Year], 'Date'[Month] ),
        'Date'[Day_Count] = 1
    )
)

    Result is as below.

    Or you can create a new calcualted table by SUMMARIZE() function.

    Date Table with Count = 
SUMMARIZE (
    'Date',
    'Date'[Year],
    'Date'[Month],
    "Count Business Day",
        CALCULATE (
            COUNT ( 'Date'[Date] ),
            FILTER (
                ALLEXCEPT ( 'Date', 'Date'[Year], 'Date'[Month] ),
                'Date'[Day_Count] = 1
            )
        )
)

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

2 Replies

  • Alf94's avatar
    Alf94
    Solution Supplier
    3 years ago

    Hi Roach_1337 ,

     

    You can create a measure with the following code:

     

    # of business days =
CALCULATE( SUM( Table[Day_Count] ) )

     

    If I answered your question, please mark my post as a solution.

     

    Best,

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Roach_1337 ,

     

    I think you want to use the value of count business day.

    I think you can try below code to create a measure or calculated column. You need to add a Year and Month column in your Date table to help calculation.

    Count Business Day =
CALCULATE (
    COUNT ( 'Date'[Date] ),
    FILTER (
        ALLEXCEPT ( 'Date', 'Date'[Year], 'Date'[Month] ),
        'Date'[Day_Count] = 1
    )
)

    Result is as below.

    Or you can create a new calcualted table by SUMMARIZE() function.

    Date Table with Count = 
SUMMARIZE (
    'Date',
    'Date'[Year],
    'Date'[Month],
    "Count Business Day",
        CALCULATE (
            COUNT ( 'Date'[Date] ),
            FILTER (
                ALLEXCEPT ( 'Date', 'Date'[Year], 'Date'[Month] ),
                'Date'[Day_Count] = 1
            )
        )
)

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.