Only count dates where the person is employed

[ThomasWeppler]

Hi Power Bi community

I have three tables a
A. A Calender table and a table that shows all the dates. [Calender]

B. A table that shows all users [users]
C. A table that shows all time registations. [registrations]

I use this DAX code to show how much each employee should work each week.

Total Normeret =

var _countmonday = calculate(COUNT(Calender[Date]), Calender[weekday] = 1)

var _counttuesday = calculate(COUNT(Calender[Date]),Calender[weekday] = 2)

var _countwednesday = calculate(COUNT(Calender[Date]), Calender[weekday] = 3)

var _countthursday = calculate(COUNT(Calender[Date]), Calender[weekday] = 4)

var _countFriday = calculate(COUNT(Calender[Date]), Calender[weekday] = 5)

var _countsaturday = calculate(COUNT(Calender[Date]), Calender[weekday] = 6)

var _countsunday = calculate(COUNT(Calender[Date]), Calender[weekday] = 7)

var _totalmonday = CALCULATE(SUM('All Users'[monday])) * _countmonday

var _totaltuesday = CALCULATE(SUM('All Users'[tuesday]))* _counttuesday

var _totalwednesday = SUM('All Users'[wednesday]) * _countwednesday

var _totalthursday = SUM('All Users'[thursday]) * _countthursday

var _totalFriday = SUM('All Users'[Friday]) * _countFriday

var _totalsaturday = SUM('All Users'[saturday]) * _countsaturday

var _totalsunday = SUM('All Users'[sunday]) * _countsunday

var _total = _totalmonday + _totaltuesday + _totalwednesday + _totalthursday + _totalFriday + _totalsaturday + _totalsunday

return

_total

But I don't want to include employees after they are no longer employed.
In the user table I have a column that shows the deactivation dates. I don't want to count dates after the users deactivation date. Any idea on how this could be solved?

All help will be greatly appreciated.

[Ashish_Mathur]

Hi,

Share an MS Excel file with the 3 tables and on another tab show the expected result.  Show your formulas/Pivot Tables so that i can translate those formulas/ogic into the DAX language.

[Anonymous]

Hi @ThomasWeppler ,

Before counting, you can filter the date table to get a new date table before the deactivation date. Therefore, the expression can be modified to:

Total Normeret =
var new_date =
FILTER(
'Calender',
'Calender'[Date] <= MAX('All Users'[DeactivationDate])
)
VAR _countmonday = CALCULATE(COUNT(new_date[Date]),new_date[weekday] = 1)
…

For detailed information about the filter function, please refer to the documentation: FILTER function (DAX) - DAX | Microsoft Learn.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[ThomasWeppler]

Thanks for the answer @Anonymous 
It sounds like the right solution. However when I try it I get the following error message.
The expression refers to multiple columns. It is not possible to convert multiple columns to a scalar value.

I did a bit of googleling and looked around on the forum, but I am not sure how to proceed from here.
Do you know how I solve it?

[Anonymous]

Hi @ThomasWeppler ,

I wonder if you would like to hide your sensitive data, or create representative data according to your needs, and then share the pbix file with me. You can also share representative screenshots or data to better help you solve the problem.

[ThomasWeppler]

@Anonymous sure

 let us say the user with name 1 doesn't work for the comany any longer. the normeret time should be zero

and let us say that the user with name 5 stoped working in the middle of the time defined by the calender slicer the normeret time should be half.

I hope this help.