Count working days of running month [Friday is weekend only] & Days elapsed

[mamunabdullah]

Hi Experts,

Please help me out!

Struggling to create a measure which will return total working days of current month dynamically [please note, Friday is only weekend in my organization]. I would also want to exclude holidays.  I have a calendar table [see scrrenshot], now I want to calculate two measures

1. Total working days of current month
2. Days Elapsed = First Date of current month - Today () -1 [Excluding weekends and holidays] 

Thans in advance
Mamun

[user_dil]

Hi,

I also have a similar requirement in my Power BI report. I want to know, in case of getting the Elapsed percentages for previous months using a slicer/filter how can we change these measures to caluculate the % dynamically? 
When I tried this measure for my requirement it only gives the correct value for the current month not an elapsed percentage of 100% for the previous month/s.

[mamunabdullah]

Hi @user_dil 
you can use variables so your slicer can get you prefered data.  

Here's my dax which doesnt return days elapsed of previous months. This is FYI

Days Elapsed in current Month =

CALCULATE(

    SUM( 'Calendar'[Is working day] ),

    'Calendar'[Date] < TODAY(),

    'Calendar'[Year] = YEAR( TODAY() ) && 'Calendar'[Month] = MONTH( TODAY() )

)

[selimovd]

Hey @mamunabdullah ,

to get the working days the following measure should help you:

Amount working day =
CALCULATE(
    COUNTROWS( Calendar ),
    Calendar[Day Name]
        IN {
        "Monday",
        "Tuesday",
        "Wednesday",
        "Thursday"
    }
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[mamunabdullah]

Hi @selimovd 

Thanks for your time, the measure you suggested retuns total days from entire date range. What I was looking for is total number of working days of current month. E.g 27 is total working days in August if you exclude Fridays. Plus 15th August is the local national holiday, so I would like to exclude this too. 

[selimovd]

Hey @mamunabdullah ,

sure, you have to put the month name in the matrix/table and then add the measure I provided. Then it will show by month name.

Where do you have the information that 15th of August is a holiday in the data?

Best regards

Denis

[mamunabdullah]

Hi @selimovd, 
Sorry to bother you again, let me tell you what I really need here. I want to see days elapsed percentage [=Days Elapsed/Total Working Days in current month] to compare with my sales achievement. Also with Days Elapsed measure, I would be able to calculate daily average orders till date. 

Answering to your question: I have created a separate table as Holidays2021

Thanks 

Mamun

[selimovd]

Hey @mamunabdullah ,

then you have to integrate that to the calendar table. Maybe add that to the calculated column. If you give more information I can help you.

Your calculation should not be the problem later, that is kind of easy.

Best regards

Denis

[selimovd · Accepted solution]

Hey @mamunabdullah ,

I think I could solve your issue.
In your Calendar table add a new calculated column with the following formula:

Is working day = 
VAR vIsRegularWorkingDay =
    IF(
        WEEKDAY( 'Calendar'[Date], 2 )
            IN { 1, 2, 3, 4 },
        1,
        0
    )
VAR vIsHoliday =
    IF(
        RELATED( Holidays2021[Date] ) <> BLANK(),
        "Holiday",
        "No Holiday"
    )
RETURN
    IF(
        vIsRegularWorkingDay = 1 && vIsHoliday = "No Holiday",
        1,
        0
    )

You can also split that into 3 columns (Is regular Working Day, Is Holiday and Is Working Day.

Like this you will have all working days and also subtracted by the holidays.

Then for the elapsed days in current month I would split it in 3 measures:

1. The working days in the current month:

Working Days in current Month =
CALCULATE(
    SUM( 'Calendar'[Is working day] ),
    'Calendar'[Year] = YEAR( TODAY() ) && 'Calendar'[Month] = MONTH( TODAY() )
)

2. The elapsed days in current month:

Days Elapsed in current Month =
CALCULATE(
    SUM( 'Calendar'[Is working day] ),
    'Calendar'[Date] <= TODAY(),
    'Calendar'[Year] = YEAR( TODAY() ) && 'Calendar'[Month] = MONTH( TODAY() )
)

3. The measure that returns the percentage in the current month:

Days Elapsed percent =
DIVIDE(
    [Days Elapsed in current Month],
    [Working Days in current Month]
)

I think like this you should be able to solve all of your issues.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic