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
ThomasWeppler
Post Prodigy
Post Prodigy

Only count dates where the person is employed

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.

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-kaiyue-msft
Community Support
Community Support

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.

Thanks for the answer @v-kaiyue-msft 
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?

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.

 

 

@v-kaiyue-msft sure

name 1 to 5.png

 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.

Helpful resources

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

Top Solution Authors