Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mamunabdullah
Frequent Visitor

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

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] 
Calendar Table.png

 

 

 

 

 

 

 

 

 

 

 

 

 

Thans in advance
Mamun

1 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
 

View solution in original post

8 REPLIES 8
user_dil
Frequent Visitor

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.

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
Super User
Super User

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
 

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. 


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

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

mamunabdullah_0-1629486100372.png

 

Thanks 

Mamun


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

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
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors