Grouping daily hours by employee/month

[Power_It_Up]

Hello.  I have been on this problem for nearly two days.  I've searched on this forum and others for an answer.  Have tried all manner of calcs using SUMX, CALCULATE, GROUP, EVALUATE, SUMMARIZE, FILTER the list goes on.

The below table is a screenshot for Employee A (we have hundreds) and how many hours a day they work.  For this, I just pulled in the Hours field and chose DON'T SUMMARIZE.  This is correct:-

Now, do you think I can make those hours add up correctly for January (should be 146.74, Mon to Fri, 23 days @ 6.38):-

Using SUM, AVERAGE..... nothing.

    

The data source is a custom CTE sql joining two tables, hence why I tried the above DAX options.

Have tried using a Calendar Table, but nothing.

Many thanks for your help in advance. 🙂

[Idrissshatila · Accepted solution]

Hello @Power_It_Up ,

what if you extracted the month from the date and show the sum of hours by month, wouldn't this work ?

add a calculated column to get the month:

month name = format( datecolumn,"MMMM")

[LeandroDeodato · Accepted solution]

i think you need convert you hours column in seconds, than sum, and convert again in hours. 

[Power_It_Up]

@Idrissshatila @LeandroDeodato @v-yohua-msft 

Thank you all for your suggestions, which I applied but still couldn't quite get the calc to work.

I decided to dismantle the merge join and split them into separate tables and applying a relationship between them.  This fixed the issue.  Thanks again. 🙂

[v-yohua-msft · Accepted solution]

Hi, @Power_It_Up 

Based on your information, I create a sample table:

Then create a new measure and try the following dax:

TotalHours = 
CALCULATE(
    SUM('Table'[Hours]),
    FILTER(
        'Table',
        'Table'[Attend Date] >= DATE(2024, 1, 1) &&
        'Table'[Attend Date] <= DATE(2024, 1, 31) &&
        WEEKDAY('Table'[Attend Date], 2) <= 5
    )
)

Here is my preview:

The reason why the January data is different from your 146.74 is because I see that you have a holiday on January 1st and New Year's Day, so the total number of working days this month is 22 days, and it is not counted as 23 days.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[LeandroDeodato · Accepted solution]

i think you need convert you hours column in seconds, than sum, and convert again in hours. 

[Idrissshatila · Accepted solution]

Hello @Power_It_Up ,

what if you extracted the month from the date and show the sum of hours by month, wouldn't this work ?

add a calculated column to get the month:

month name = format( datecolumn,"MMMM")