Calculate number of working days for each company

[Anonymous]

Hi,
 
My company is operating in different countries, so I have added few columns in my Dim_Date table like 'IsHolidayUSA', 'IsHolidayUK' etc. All the weekends and public holidays are populated for each country. Please see how my date dimension looks like in the attached image.
 
Now I want to create a report where I want to see all my companies and their working days until current date.
E.g. my company in USA has 285 working days from 01Jan2020 until today (where IsHolidayUSA = False).
      my company in UK has 283 working days from 01Jan2020 until today(where IsHolidayUK = False).
Is it possible to create a dax measure which will count the days from the first day of the year until current date and apply country filter according to the company?

[Anonymous]

@Anonymous -What I could suggest is below . You need to create this for each country and I am assuming you have a date table

WorkingDayNumber =
VAR thisdate = 'Date'[Date]
VAR thisyear = 'Date'[Year]
RETURN
IF (
'Date'[IsWorkingDay] = 1,
CALCULATE (
SUM ( 'Date'[IsWorkingDay] ),
'Date'[Year] = thisyear,
'Date'[Date] <= thisdate
)
)

Appreciate  your kudos!! If this works for you, please mark it as the solution. 

[v-deddai1-msft]

Hi @Anonymous ，

Did you have a country column in your company table，if so，you can use switch measure :

workday =
SWITCH (
    'Company'[Country],
    "USA",
        CALCULATE (
            COUNT ( 'Dim_Date'[BK_Date] ),
            FILTER (
                'Dim_Date',
                'Dim_Date'[BK_Date] <= TODAY ()
                    && 'Dim_Date'[IsHolidayUSA] = 0
            )
        ),
    "UK",
        CALCULATE (
            COUNT ( 'Dim_Date'[BK_Date] ),
            FILTER (
                'Dim_Date',
                'Dim_Date'[BK_Date] <= TODAY ()
                    && 'Dim_Date'[IsHolidayUK] = 0
            )
        )......
)

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

Best Regards,

Dedmon Dai

[Anonymous]

@NI unfortunately, I do not want to create seperate measure for each country. What I was looking to create one measure that could calculate the total working days from the start of the year until today by each company.

The reason is that later I want to use that measure (lets call it "sum of working days") in other calculation.

I already have a dataset of timesheet where employees have entered their working hours (measure called "sum of hours worked").

The goal is to create an Avgerage Daily working hours measure which will be "sum of hours worked" / "sum of working days". 

[mahoneypat]

That is possible but to do it in a single measure you will either need to unpivot your Date table or write a SWITCH that basically would contain all the individual measures.

Regards,

Pat

[amitchandak]

@Anonymous , with help from date table use time intelligence

YTD Holiday USA= CALCULATE(SUM('Date'[IsHolidayUSA]),DATESYTD('Date'[Date],"12/31"))

YTD Holiday UK= CALCULATE(SUM('Date'[IsHolidayUK]),DATESYTD('Date'[Date],"12/31"))

Same way for others

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.