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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
snifer
Post Patron
Post Patron

WORKING DAYS - HOLIDAYS IN MY POWERBI DATA MODEL

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

First is company details

company codecompany namenation
1testDK
2test 1DE
4test 4DK
3test 3LT

 

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

company codenationdateamounth
1DK01/01/20201
1DK02/01/20201
1DK04/01/20201
2DE05/01/20202
2DE03/01/20202
4DK01/01/202022
4DK01/01/20202
4DK07/01/20201
4DK05/01/20201
4DK02/01/20205
5LT04/01/20201

 

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)

daten day weekworking days
01/01/202011
02/01/202021
03/01/202031
04/01/202041
05/01/202051
06/01/202060
07/01/202070

 

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

countrydatedescription 
DK01/01/2020 new year 
DE01/01/2020new year 
DE03/01/2020national 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

1 ACCEPTED SOLUTION

Hi @snifer ,

 

here is a DAX solution. 

Workingday.png

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


View solution in original post

12 REPLIES 12

Hi @snifer ,

 

here is a DAX solution. 

Workingday.png

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


Jimmy801
Community Champion
Community Champion

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

based on the date table and the holiday table

countrydatedescription 
DK01/01/2020 new year 
DE01/01/2020new year 
DE03/01/2020national 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
Community Champion
Community Champion

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

@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
Community Champion
Community Champion

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

@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
Community Champion
Community Champion

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
Solution Supplier
Solution Supplier

Hey @snifer ,

Have a doubt,
What does mean by 
sum n days <6
 into working days column in calendar table ?
 
Please clarify 
 
Regards,
Snandy

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
Solution Sage
Solution Sage

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





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


@kentyler 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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