Forum Discussion

AlexDawson1111's avatar
AlexDawson1111
New Member
3 years ago
Solved

SUM on Distinct Month

Hi,

 

I have the following data below. I am trying to calculate the total distinct EmpPeriodHrs per month. So in this case i have 4 distinct payroll end dates for Payroll End Date that fall in the month of October.. I want to have a column or measure that calculates this. My goal is to have a column or measure that shows 160 repeatedly in this example. This could be different for each of my employees aswell (i have another column for employee id)

 

Thanks

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi AlexDawson1111 ,

    According to your description, I create a sample with two employees.

    Here's my solution, create a calculated column.

    Column =
CALCULATE (
    SUM ( 'Table'[Hours] ),
    FILTER (
        'Table',
        EOMONTH ( 'Table'[Payroll End Date], 0 )
            = EOMONTH ( EARLIER ( 'Table'[Payroll End Date] ), 0 )
            && 'Table'[Employee ID] = EARLIER ( 'Table'[Employee ID] )
    )
)

    Result:

    If you want to display the result in a visual, a measure can also work:

    Measure =
CALCULATE (
    SUM ( 'Table'[Hours] ),
    FILTER (
        ALL ( 'Table' ),
        EOMONTH ( 'Table'[Payroll End Date], 0 )
            = EOMONTH ( MAX ( 'Table'[Payroll End Date] ), 0 )
            && 'Table'[Employee ID] = MAX ( 'Table'[Employee ID] )
    )
)

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

4 Replies

  • pbi-novice's avatar
    pbi-novice
    Helper I
    3 years ago

    I am a little confused on what you are trying to achieve.

     

    Correct me if I'm wrong:

     

    Input

    10/02 -> 40hrs

    10/09 -> 40hrs

    10/16 -> 40hrs

    10/23 -> 40hrs

     

    Result

    October 2022 -> 160hrs

    • AlexD123's avatar
      AlexD123
      New Member
      3 years ago

      Hi thanks for the reply. That is correct. However I want my empleriodhrs field to show the 160 

  • pbi-novice's avatar
    pbi-novice
    Helper I
    3 years ago

    Hi Alex,

     

    This can be solved using Power Query(I don't know DAX well enough to give that solution, I'm sure someone else can chime in for that).

     

    Steps:

    1. Added a "MonthYearCode" column to the original "Table".
      1. This column combined month and year as a text. 10/23/22 -> 102022
    2. Duplicated "Table"
    3. Removed duplicates from the Payroll End Date
    4. Group By:
      1. "MonthYearCode"
      2. Sum "EmpPeriodHrs" as "TotalEmpPeriodHrs"
    5. Go to original "Table" and merge by "MonthYearCode" column.
    6. Expand the table (You only need to select "TotalEmpPeriodHrs")

    Final Result of Duplicate Table

     

    Final Result of original Table

     

    This should will give you the total payroll hours by month and year per row.

     

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi AlexDawson1111 ,

    According to your description, I create a sample with two employees.

    Here's my solution, create a calculated column.

    Column =
CALCULATE (
    SUM ( 'Table'[Hours] ),
    FILTER (
        'Table',
        EOMONTH ( 'Table'[Payroll End Date], 0 )
            = EOMONTH ( EARLIER ( 'Table'[Payroll End Date] ), 0 )
            && 'Table'[Employee ID] = EARLIER ( 'Table'[Employee ID] )
    )
)

    Result:

    If you want to display the result in a visual, a measure can also work:

    Measure =
CALCULATE (
    SUM ( 'Table'[Hours] ),
    FILTER (
        ALL ( 'Table' ),
        EOMONTH ( 'Table'[Payroll End Date], 0 )
            = EOMONTH ( MAX ( 'Table'[Payroll End Date] ), 0 )
            && 'Table'[Employee ID] = MAX ( 'Table'[Employee ID] )
    )
)

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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