Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everybody
I'm trying to find the formula to calculate the number of employees signed in during each hour of the day and the data will be refreshed very frequently during the day. As you can see in the screenshot it can find how many were signed in at 9:00 but then for the rest of the day it shows the value 4.
In the table on the right side the "Type" column determines if the row represents an sign in (I) or sign out (P)
In this table one employee (01885) has signed in and out for the day
Can anybody help me fix my formula so it shows no values for the timeslots which have not happened yet?
CountofActiveEmployees =
VAR CurrentTime = MAX(Retail[DateTime])
RETURN
COUNTROWS(FILTER('REGISTRATIONS',
CurrentTime >= REGISTRATIONS[INDATETIME] && CurrentTime <= REGISTRATIONS[OUTDATETIME]))
Best regards
Eva
Solved! Go to Solution.
Hi @evalindag ,
Try the following measure:
CountOfActiveEmployess =
VAR temtable =
GROUPBY (
'REGISTRATIONS';
'REGISTRATIONS'[Division];
'REGISTRATIONS'[EMPLOYEENO];
'REGISTRATIONS'[INDATETIME];
"OUT"; MAXX ( CURRENTGROUP (); 'REGISTRATIONS'[OUTDATETIME] )
)
VAR exit_time =
FILTER ( temtable; [OUT] <> BLANK () )
VAR no_exit =
FILTER ( temtable; [OUT] = BLANK () )
VAR CurrentTime =
MAX ( Retail[DateTime] )
RETURN
COUNTROWS (
FILTER (
exit_time;
'REGISTRATIONS'[INDATETIME] <= CurrentTime
&& [OUT] >= CurrentTime
)
)
+ COUNTROWS ( FILTER ( no_exit; 'REGISTRATIONS'[INDATETIME] <= CurrentTime ) )
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @evalindag ,
We can create a measure to meet your requirement.
CountofActiveEmployees =
VAR CurrentTime = MAX(Retail[DateTimeGroup])
var _nexthourtime = CurrentTime + TIME(1,0,0)
var _min_date = CALCULATE(MIN(Retail[DateTimeGroup]),ALLSELECTED(Retail))
var _x =
CALCULATE(
DISTINCTCOUNT(REGISTRATIONS[EM]),
FILTER('REGISTRATIONS',
REGISTRATIONS[INDATETIME] >= _min_date && REGISTRATIONS[INDATETIME]<_nexthourtime))
var _y =
CALCULATE(
DISTINCTCOUNT(REGISTRATIONS[EM]),
FILTER('REGISTRATIONS',
NOT(ISBLANK(REGISTRATIONS[OUTDATETIME]))&&
REGISTRATIONS[OUTDATETIME] >= _min_date && REGISTRATIONS[OUTDATETIME]<_nexthourtime ))
return
_x-_y
If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?
It will be helpful if you can show us the exact expected result based on the tables.
Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @evalindag ,
We can create a measure to meet your requirement.
CountofActiveEmployees =
VAR CurrentTime = MAX(Retail[DateTimeGroup])
var _nexthourtime = CurrentTime + TIME(1,0,0)
var _min_date = CALCULATE(MIN(Retail[DateTimeGroup]),ALLSELECTED(Retail))
var _x =
CALCULATE(
DISTINCTCOUNT(REGISTRATIONS[EM]),
FILTER('REGISTRATIONS',
REGISTRATIONS[INDATETIME] >= _min_date && REGISTRATIONS[INDATETIME]<_nexthourtime))
var _y =
CALCULATE(
DISTINCTCOUNT(REGISTRATIONS[EM]),
FILTER('REGISTRATIONS',
NOT(ISBLANK(REGISTRATIONS[OUTDATETIME]))&&
REGISTRATIONS[OUTDATETIME] >= _min_date && REGISTRATIONS[OUTDATETIME]<_nexthourtime ))
return
_x-_y
If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?
It will be helpful if you can show us the exact expected result based on the tables.
Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @evalindag ,
Try the following measure:
CountOfActiveEmployess =
VAR temtable =
GROUPBY (
'REGISTRATIONS';
'REGISTRATIONS'[Division];
'REGISTRATIONS'[EMPLOYEENO];
'REGISTRATIONS'[INDATETIME];
"OUT"; MAXX ( CURRENTGROUP (); 'REGISTRATIONS'[OUTDATETIME] )
)
VAR exit_time =
FILTER ( temtable; [OUT] <> BLANK () )
VAR no_exit =
FILTER ( temtable; [OUT] = BLANK () )
VAR CurrentTime =
MAX ( Retail[DateTime] )
RETURN
COUNTROWS (
FILTER (
exit_time;
'REGISTRATIONS'[INDATETIME] <= CurrentTime
&& [OUT] >= CurrentTime
)
)
+ COUNTROWS ( FILTER ( no_exit; 'REGISTRATIONS'[INDATETIME] <= CurrentTime ) )
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |