counting working days

[snifer]

Hi, I got my report where I ve a list of the company and each company has a column where is specified their country where they are located.

How I can create or import a calendar and create a measure for calculating the number of the working days specific for each country?

anybody can help?

[mwegener]

Hi @snifer ,

I have created an example for you.

Define a base calendar code (possibly company)...

...and then the non working days for the base calendar.

On this base, the PBIX determines the working days.

You may download my PBIX and Excel file from here.
Hope this helps.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[snifer]

@mwegener 

thanks for the solution provided i think might work, 

I send you the data model ive in my report, the calendar table is created in Dax, not in query mode and there is the company table,

I wish to calculate working days based on a country column in the company table since all companies in the same country will have the same working days could you help me using the attached PBI file to build the working days calculation?

https://www.dropbox.com/s/7pw7fkn7u0w3xef/TEST.pbix?dl=0

[mwegener]

Hi @snifer,

try this.

PBIX

EXCEL

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[snifer]

@mwegener 

 I still not get how you create the steps from import the excel file to the final pbi table "Base Calendar",

is it possible for you to create a video in order to make me understand your steps to have the column working days in the base calendar table?

[mwegener]

Hi @snifer,

unfortunately, the effort is too high for this free support forum.

This is a different kind of consulting.

https://kumavision.com/en/bi/power-bi

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[mwegener]

Hi @snifer ,

in ERP system Business Central (formerly Dynamics NAV), there is a Base Calendar Change table in which all nonworking days are stored with a recurring system, for each company.

[mwegener]

Hi @snifer, 

has your question been answered?

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[snifer]

I don't have an erp system for count working days, I need to build on powerbi

[Mariusz]

Hi @snifer 

You can lookup bank holidays from this website 
https://www.timeanddate.com/holidays/ with Query Editor and all other internal working patterns ( like shift patterns ) will have to come from your HR system.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.