cancel
Showing results 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

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

1 ACCEPTED SOLUTION
MVP

Hi @snifer ,

here is a DAX solution.

Hope this helps.

Regards,

Marcus

Dortmund - Germany

Please mark my post as solution, this will also help others.

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."

12 REPLIES 12
MVP

Hi @snifer ,

here is a DAX solution.

Hope this helps.

Regards,

Marcus

Dortmund - Germany

Please mark my post as solution, this will also help others.

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."

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

Post Patron

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

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

Post Patron

@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

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

Post Patron

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

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

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

Solution Supplier

Hey @snifer ,

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

Regards,
Snandy
Post Patron

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

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

Post Patron

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