Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
evalindag
Frequent Visitor

Find the number of signed in employees today

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]))

 

 

Snag_1ea3bd1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best regards

Eva

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

v-zhenbw-msft
Community Support
Community Support

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

 

find1.jpg

 

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.

View solution in original post

3 REPLIES 3
v-zhenbw-msft
Community Support
Community Support

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

 

find1.jpg

 

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 @v-zhenbw-msft & @MFelix thank you very much, both of your solutions work 🙂

 

Best regards

Eva

MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.