WORKING DAYS - HOLIDAYS IN MY POWERBI DATA MODEL

[snifer]

My powerbi data model bi is so composed, I got  3 tables

First is company details

company code	company name	nation
1	test	DK
2	test 1	DE
4	test 4	DK
3	test 3	LT

second table I got the transaction of the company with the date:

company code	nation	date	amounth
1	DK	01/01/2020	1
1	DK	02/01/2020	1
1	DK	04/01/2020	1
2	DE	05/01/2020	2
2	DE	03/01/2020	2
4	DK	01/01/2020	22
4	DK	01/01/2020	2
4	DK	07/01/2020	1
4	DK	05/01/2020	1
4	DK	02/01/2020	5
5	LT	04/01/2020	1

3 table is a calendar i created and I connected to date in a transaction table for filter the period:

working days= in powerbi you can assign a number of the day of the week 1 to 7  Monday to Sunday, to calculate the working days I sum the working days from 1 to 5 workingdays= sum (days of the week), filter days <6)

date	n day week	working days
01/01/2020	1	1
02/01/2020	2	1
03/01/2020	3	1
04/01/2020	4	1
05/01/2020	5	1
06/01/2020	6	0
07/01/2020	7	0

now I want to create a 4 table called Holidays where I list non-work days (holidays) based on the country and make the working days subtract the days that in that period are red days.

something like this

country	date	description
DK	01/01/2020	new year
DE	01/01/2020	new year
DE	03/01/2020	national holiday

how I can create a measure that considers the date in holidays table, and subtract working days to their country holidays?

so for the first week of January those are the expected results

EXPECTED RESULTS:

FOR

DK  4 DAYS

LT  5 DAYS

DE  3 DAYS

[mwegener · Accepted solution]

Hi @snifer ,

here is a DAX solution. 

You may download my PBIX 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.

[Jimmy801]

Hello @snifer 

I don't get the idea, were exactly you are calculating any working/nonworking days.

So, how exactly are calculated these numbers?

EXPECTED RESULTS:

FOR

DK 4 DAYS

LT 5 DAYS

DE 3 DAYS

Jimmy

[snifer]

based on the date table and the holiday table

country	date	description
DK	01/01/2020	new year
DE	01/01/2020	new year
DE	03/01/2020	national holiday

( for the sake of the example lets assume  as specified from date table 01/01/2020 as monday)

so 1 week: 5 working days

for Dk 5 days -1 holiday day

for De 5 working day - 2 holidays

for LT 5 working days - 0 holidays

[Jimmy801]

Hello @snifer 

okay, so the basis would be a datetable, grouped by weeks. And then subtract dates from the holiday-table.

Question: What has this to do with the transaction or company code table and what are the total working days per week for? What is the scope of this new table/calculation?

BR

Jimmy

[snifer]

@Jimmy801  Sorry I forget to describe how the final report works :

the report is filtered by company code  from company table

and for the period filter will be using a slicer from from date table

so I could calculate the amount from a different company (company code) with the period I select

now I can calculate working days but I need to add holidays  as I described

[Jimmy801]

Hello @snifer 

sorry, but I'm still not there...

so you select a period 04.01.19 - 20.01.19... but where are coming in the holidays here?

Or do you select a date and a duration of working days and want to have the amount of transaction within these working days?

BR

Jimmy

[snifer]

@Jimmy801 

report start from 01/01/2020 you cant select 2019

selecting the company code and the period, for example, January it will say that working days for the period selected from 1 to 7 January 2020 are 5 days and holidays 1 for DK so 4 working days,

for the amount, I made other calculations that are working fine...

I just need to make a measure( or something that will do the trick) that will consider what company code is selected and his specific country ( every company have specific country as for company table) and checking the holidays' table to see if that specific country have holidays day in the selected period and subtract from working days

[Jimmy801]

Hello @snifer 

I've prepared a solution for you. This involves also Power Query, that creates, based on a matrix with country and holiday, a new table with all working days for every country.

When the data is connected, a simple measure with countrows does the trick

here the link to the solution link 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[sanalytics]

Hey @snifer ,

Have a doubt,

What does mean by 

sum n days <6

 into working days column in calendar table ?

Please clarify 

Regards,

Snandy

[snifer]

in powerbi you can assign a number of the day of the week 1 to 7  Monday to Sunday so to calculate the working days I sum the working days from 1 to 5 workingdays= sum (days of the week), filter days <6)

this measure working fine

[kentyler]

Here is a past post that explains one solution in detail. Maybe it will help 

https://community.powerbi.com/t5/Desktop/NETWORKDAYS-with-multiple-country-holidays/td-p/401636

[snifer]

@kentyler 

it is similar case, but I can't crack the solution for mine