Using a slicer to calculate values in column

[strtl123]

Hello,

if am having a peculiar Problem to which the solution might be very simple using some specific formula or very complicated using a huge amount of columns. Here it is:

The Key purpose is to compare the amount of hours a worker worked (part 1) to the hours a worker should have worked (part 2). I want to use a slicer to change the period which is to be observed.

For part 1 the solution is very simple as i have a table with individual entries for the times worked so it s just a sum funktion where i can use the date slicer...:

Name	Date	Hours    Worked
David	21.09.2021	8.5
David	22.09.2021	6
David	23.09.2021	8
John	22.09.2021	5
John	23.09.2021	7
Sarah	23.09.2021	8

For the second part I currently have a date table (for the slicer), a Table with the Workers and a Table with absentee entries which are to be subtracted from the "Should Work Hours", as one obviously does not need to work when sick or on vacation...

Worker Table:

Name	email	Should Work Hours
David	david@email.com	XXXXX
John	John@email.com	XXXXX
Sarah	sarah@email.com	XXXXX

Absentee Table

Name	From	To	Reason
John	21.09.2021	21.09.2021	Sick
Sarah	21.09.2021	22.09.2021	Vacation

Should work hours must be 8 hours per day minus saturdays and sunday minus holidays minus vacation.

Now I would like to calculate the "Should Work Hours" column according to the slicer for a variable period (the solution for a fixed period is simple). The result should be:

For the Slicer = 21.09.2021 to 21.09.2021

Name	HasWorked	ShouldWorkHours
David	8.5	8
John	0	0
Sarah	0	0

For the Slicer = 21.09.2021 to 23.09.2021

Name	HasWorked	ShouldWorkHours
David	22,5	24
John	12	16
Sarah	8	8

I know I could make a cloumn for each Worker in the Date Table and subtract the off days, sum them up and multiply by 8 but as the real data contains a lot more workers this would be inconvenient. Maybe someone has a better solution?

Thanks!

[v-henryk-mstf]

Hi @strtl123 ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.

If not, please point it out. I will answer for you as soon as possible. Looking forward to your reply.

Best Regards,
Henry

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

[strtl123]

Seems like a solution altough i cannot access [From] and [To] in a measure...

[Greg_Deckler]

@strtl123 You cannot create a column for that as columns are not dynamic and only calculate at time of refresh. You would need to use a measure. 

[strtl123]

Ok but how do i create a measure with a dynamic filter value?

[Greg_Deckler]

@strtl123 Well, assumine you have Name column in your visual from your Worker Table, it would be something like:

Measure =
  VAR __Name = MAX('Table'[Name])
  VAR __VacationDays =
    COUNTROWS(
      FILTER(
        ADDCOLUMNS(
          GENERATE(
            FILTER('Absentee Table',[Reason]="Vacation" || [Reason]="Holiday"),
            GENERATESERIES([From],[To],1)
          ),
          "__Weekday",WEEKDAY([Value],2)
        ),
        [__Weekday]<6
      )
    )
  VAR __WorkDays = COUNTROWS('Calendar',[Weekday]<6)
RETURN
  __WorkDays * 8 - __VacationDays * 8

[strtl123]

Thank you for your reply. Your code seems logical but Power BI doesn't let me choose the [From] and [To] columns in GENERATESERIES