Calculate number of working days for every provider for a month

[Anonymous]

I have two tables as you can see in the above image. I want to create a table in Powerbi that would show me the number of days each worker actually worked in a month. I have a slicer that enables me to select any desired month. I have a card that also shows the number of working days in a month. So my desired output should be : - When I select Month January from my slicer -
1) Number of working Days - 20 (this card visual is done)

2) Table showing me   AD - 15 days (5+5+4), BC - 13 days (5+4+4) and so on.

I don’t know how I should go about the second part? Need help.

[Anonymous]

Hi @v-rzhou-msft ,

Actually there is a modification to the dataset. 

I have a new column that tells me actual days worked by every worker. That column is just number of hours divided by 8. So now I want two visual cards and two slicers :

1 - Visual card displaying actual working days in a month (say for example January - 22, February - 20 and so on)

2 - Visual card showing me actual number of days worked by every worker for all the months. For example AD in January = (1.25*5) + (1.25*4) + (1.25*4) = 16.25 

3 - Common Slicer to select any month and should affect both visuals

4 - Slicer to select the workers.

Thanks for all the help till now. Appreciate that.

[Anonymous]

Hi @v-rzhou-msft ,

Can you help me this issue?

[Anonymous]

Hi @v-rzhou-msft,

I have figured out the issue. My slicer is now working properly. When I select a specific month and a worker name, I am able to see how many days the worker is actually working. Now what I want is two card visuals based on the same month slicer. So if I select January from the slicer, one of my card visuals should show me the number of working days in that month(Mon-Fri exclude Sat and Sun) say for example it shows 22 for January and then when I select any worker name from my other slicer then first card should continue showing 22 and my other card visual would show me actual number of days that specific worked worked for the month of January. This should be based on the same slicer name 'MONTH'. How should I go about this?

[Anonymous]

Also there is a calender table for the year 2023. How will all three tables hold relationship between them?

[v-rzhou-msft]

Hi @Anonymous ,

According to your statement, I think your issue should be caused by your data model.

You can download my attatchment file and check the difference between mine and yours.

If you still couldn't find the solution, please share a sample file with me without sensitive data. 

This will make it easier for us to find the solution.

Best Regards,
Rico Zhou

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

[Anonymous]

Okay, Sure. I will check and give you my updates.

[Anonymous]

Hi Rico, 
Thanks for the reply and helping me out. In my slicers when I try to select both month and the name, the measure which you told to create goes blank. I don't understand why that is happening. If I only select name from my slicer, I am able to see my card, but as soon as I select any month I can't see any value for the measure.

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

I suggest you to create a relationship between two tables.

Measure:

Workingday Selecy Month = CALCULATE(MAX('Workding Schedule for the Week'[TargetPerDay]))

Result is as below.

Best Regards,
Rico Zhou

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