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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Spudder112
Helper I
Helper I

Trying to Calculate Unique 'Headcount' Measures based on Weeks an Employee was Paid.

I am trying to create some different logic to track employees that works some weeks and not others...

 

I want to add the following logic:

 

>=2 week break - Employees that have missed 2 weeks.

Returned - Employees returning after missing at least 2 weeks.

Active - Employees that have not missed either of the last 2 weeks.

 

This would be easy if I only wanted to show these measures for the current week, however I am struggling to trend these (using week ending dates).

 

Example:

 

Employee1: 

 

                                >=2WeekBreak   Returned      Active

1/7/2023 $30                     0                  0                 1

1/14/2023 $30                   0                  0                 1

1/21/2023 $0                     0                  0                 0

1/28/2023 $0                     1                  0                 0

2/4/2023 $30                     0                  1                 0

2/11/2023 $0                     0                  0                 0

2/18/2023 $30                   0                  0                 0

 

Employee2:

 

                                >=2WeekBreak   Returned      Active

1/7/2023 $30                     0                  0                 1

1/14/2023 $0                     0                  0                 0

1/21/2023 $0                     1                  0                 0

1/28/2023 $0                     1                  0                 0

2/4/2023 $30                     0                  1                 0

2/11/2023 $30                   0                  0                 1

2/18/2023 $30                   0                  0                 1

 

Desired output for the two employees:

 

                               >=2WeekBreak   Returned      Active

1/7/2023                            0                  0                 2

1/14/2023                          0                  0                 1

1/21/2023                          1                  0                 0

1/28/2023                          2                  0                 0

2/4/2023                            0                  2                 0

2/11/2023                          0                  0                 1

2/18/2023                          0                  0                 1

 

 

Current Relevant Data setup:

 

Date Table:

Date

WeekEndingDate

 

Pay Detail:

Employee ID

Pay Amount

Date

 

Please let me know if more info is needed!

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @Spudder112 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Don't create any relationship between date dimension table and fact table

vyiruanmsft_1-1683006650928.png

2. Create a measure as below to get the sum of pay amount

SumofPayAmount = 
CALCULATE (
    SUM ( 'Pay Detail'[Pay Amount] ),
    FILTER (
        'Pay Detail',
        'Pay Detail'[Date] = SELECTEDVALUE ( 'Date'[WeekEndingDate] )
    )
)

3. Create a measure as below to get the count of greater than 2 weeks break employee

Measure = 
VAR _selwe =
    SELECTEDVALUE ( 'Date'[WeekEndingDate] )
VAR _selemp =
    SELECTEDVALUE ( 'Pay Detail'[Employee ID] )
VAR _pwpamount =
    CALCULATE (
        SUM ( 'Pay Detail'[Pay Amount] ),
        FILTER (
            ALLSELECTED ( 'Pay Detail' ),
            'Pay Detail'[Employee ID] = _selemp
                && 'Pay Detail'[Date] = _selwe - 7
        )
    )
RETURN
    IF ( ISBLANK ( [SumofPayAmount] ) && ISBLANK ( _pwpamount ), 1, 0 )
GT2WeekBreak = 
SUMX (
    VALUES ( 'Pay Detail'[Employee ID] ),
    SUMX ( VALUES ( 'Date'[WeekEndingDate] ), [Measure] )
)

4. Create a measure as below to get the count of returned employee

Measure2 = 
VAR _selwe =
    SELECTEDVALUE ( 'Date'[WeekEndingDate] )
VAR _mindate =
    CALCULATE ( MIN ( 'Pay Detail'[Date] ), ALLSELECTED ( 'Pay Detail' ) )
VAR _selemp =
    SELECTEDVALUE ( 'Pay Detail'[Employee ID] )
VAR _pwpamount =
    CALCULATE (
        SUM ( 'Pay Detail'[Pay Amount] ),
        FILTER (
            ALLSELECTED ( 'Pay Detail' ),
            'Pay Detail'[Employee ID] = _selemp
                && 'Pay Detail'[Date] = _selwe - 7
        )
    )
VAR _p2wpamount =
    CALCULATE (
        SUM ( 'Pay Detail'[Pay Amount] ),
        FILTER (
            ALLSELECTED ( 'Pay Detail' ),
            'Pay Detail'[Employee ID] = _selemp
                && 'Pay Detail'[Date] = _selwe - 14
        )
    )
RETURN
    IF (
        _selwe <> _mindate
            && NOT ( ISBLANK ( [SumofPayAmount] ) ) && ISBLANK ( _pwpamount )
                && ISBLANK ( _p2wpamount ),
        1,
        0
    )
Returned = 
SUMX (
    VALUES ( 'Pay Detail'[Employee ID] ),
    SUMX ( VALUES ( 'Date'[WeekEndingDate] ), [Measure2] )
)

5. Create a measure as below to get the count of active employee

Measure3 = 
VAR _selwe =
    SELECTEDVALUE ( 'Date'[WeekEndingDate] )
VAR _mindate =
    CALCULATE ( MIN ( 'Pay Detail'[Date] ), ALLSELECTED ( 'Pay Detail' ) )
VAR _selemp =
    SELECTEDVALUE ( 'Pay Detail'[Employee ID] )
VAR _pwpamount =
    CALCULATE (
        SUM ( 'Pay Detail'[Pay Amount] ),
        FILTER (
            ALLSELECTED ( 'Pay Detail' ),
            'Pay Detail'[Employee ID] = _selemp
                && 'Pay Detail'[Date] = _selwe - 7
        )
    )
RETURN
    IF (
        (
            _selwe = _mindate
                && NOT ( ISBLANK ( [SumofPayAmount] ) )
        )
            || ( NOT ( ISBLANK ( [SumofPayAmount] ) ) && NOT ( ISBLANK ( _pwpamount ) ) ),
        1,
        0
    )
Active = 
SUMX (
    VALUES ( 'Pay Detail'[Employee ID] ),
    SUMX ( VALUES ( 'Date'[WeekEndingDate] ), [Measure3] )
)

vyiruanmsft_0-1683006605317.png

Best Regards

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

View solution in original post

3 REPLIES 3
v-yiruan-msft
Community Support
Community Support

Hi @Spudder112 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Don't create any relationship between date dimension table and fact table

vyiruanmsft_1-1683006650928.png

2. Create a measure as below to get the sum of pay amount

SumofPayAmount = 
CALCULATE (
    SUM ( 'Pay Detail'[Pay Amount] ),
    FILTER (
        'Pay Detail',
        'Pay Detail'[Date] = SELECTEDVALUE ( 'Date'[WeekEndingDate] )
    )
)

3. Create a measure as below to get the count of greater than 2 weeks break employee

Measure = 
VAR _selwe =
    SELECTEDVALUE ( 'Date'[WeekEndingDate] )
VAR _selemp =
    SELECTEDVALUE ( 'Pay Detail'[Employee ID] )
VAR _pwpamount =
    CALCULATE (
        SUM ( 'Pay Detail'[Pay Amount] ),
        FILTER (
            ALLSELECTED ( 'Pay Detail' ),
            'Pay Detail'[Employee ID] = _selemp
                && 'Pay Detail'[Date] = _selwe - 7
        )
    )
RETURN
    IF ( ISBLANK ( [SumofPayAmount] ) && ISBLANK ( _pwpamount ), 1, 0 )
GT2WeekBreak = 
SUMX (
    VALUES ( 'Pay Detail'[Employee ID] ),
    SUMX ( VALUES ( 'Date'[WeekEndingDate] ), [Measure] )
)

4. Create a measure as below to get the count of returned employee

Measure2 = 
VAR _selwe =
    SELECTEDVALUE ( 'Date'[WeekEndingDate] )
VAR _mindate =
    CALCULATE ( MIN ( 'Pay Detail'[Date] ), ALLSELECTED ( 'Pay Detail' ) )
VAR _selemp =
    SELECTEDVALUE ( 'Pay Detail'[Employee ID] )
VAR _pwpamount =
    CALCULATE (
        SUM ( 'Pay Detail'[Pay Amount] ),
        FILTER (
            ALLSELECTED ( 'Pay Detail' ),
            'Pay Detail'[Employee ID] = _selemp
                && 'Pay Detail'[Date] = _selwe - 7
        )
    )
VAR _p2wpamount =
    CALCULATE (
        SUM ( 'Pay Detail'[Pay Amount] ),
        FILTER (
            ALLSELECTED ( 'Pay Detail' ),
            'Pay Detail'[Employee ID] = _selemp
                && 'Pay Detail'[Date] = _selwe - 14
        )
    )
RETURN
    IF (
        _selwe <> _mindate
            && NOT ( ISBLANK ( [SumofPayAmount] ) ) && ISBLANK ( _pwpamount )
                && ISBLANK ( _p2wpamount ),
        1,
        0
    )
Returned = 
SUMX (
    VALUES ( 'Pay Detail'[Employee ID] ),
    SUMX ( VALUES ( 'Date'[WeekEndingDate] ), [Measure2] )
)

5. Create a measure as below to get the count of active employee

Measure3 = 
VAR _selwe =
    SELECTEDVALUE ( 'Date'[WeekEndingDate] )
VAR _mindate =
    CALCULATE ( MIN ( 'Pay Detail'[Date] ), ALLSELECTED ( 'Pay Detail' ) )
VAR _selemp =
    SELECTEDVALUE ( 'Pay Detail'[Employee ID] )
VAR _pwpamount =
    CALCULATE (
        SUM ( 'Pay Detail'[Pay Amount] ),
        FILTER (
            ALLSELECTED ( 'Pay Detail' ),
            'Pay Detail'[Employee ID] = _selemp
                && 'Pay Detail'[Date] = _selwe - 7
        )
    )
RETURN
    IF (
        (
            _selwe = _mindate
                && NOT ( ISBLANK ( [SumofPayAmount] ) )
        )
            || ( NOT ( ISBLANK ( [SumofPayAmount] ) ) && NOT ( ISBLANK ( _pwpamount ) ) ),
        1,
        0
    )
Active = 
SUMX (
    VALUES ( 'Pay Detail'[Employee ID] ),
    SUMX ( VALUES ( 'Date'[WeekEndingDate] ), [Measure3] )
)

vyiruanmsft_0-1683006605317.png

Best Regards

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

Thank you for this! I tried this solution, and it worked, but with a dataset as large as I am working with, it made my report very slow. We are currently going to work this logic into our backend, but thank you regardless!

Spudder112
Helper I
Helper I

@amitchandak I had tried refrencing your previous HR Analytics post (which has been very useful!), but still was not able to figure this out.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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