How to combine count and sum to get a monthly count

[DanT1994]

I'm looking for a DAX formula (or another method) that will give me a count of employees, per month, hitting their hours target.

Below is an example of the source data (I have a lot more names/months etc.).

On the left is the data, and on the right is the desired output.

[Anonymous · Accepted solution]

Hi

Try this measure

vb Empl sup a 7h =

VAR _step1 =

FILTER (

ADDCOLUMNS (

SUMMARIZE (

Feuil3,

Feuil3[ID]

),

"Hours",

CALCULATE (

SUM ( Feuil3[Hours] )

)

),

[Hours] >= 7

)

VAR step2 =

COUNTROWS ( _step1 )

RETURN

step2

[Anonymous · Accepted solution]

Hi Dan,

The difference between our models is that i have only 1 date column and you have 2.

Do you have a date table with a relation ship on one date column ?

[DanT1994]

James, 

I fixed my date relationships and the formula worked perfectly!

Thanks so much!

[Anonymous]

No probs !! Enjoy Now

[DanT1994]

Hi James,

See below my date table:



The relationship is as below:

The shift export only has 1 relationship with the date table.

Thanks again for the speedy response!

[Anonymous]

Sorry this is my source an excel file imported in Power BI. That is the Feuil3 in my measure.

I also work with a dtae table. 

This is the result in BI

[Anonymous · Accepted solution]

Hi

Try this measure

vb Empl sup a 7h =

VAR _step1 =

FILTER (

ADDCOLUMNS (

SUMMARIZE (

Feuil3,

Feuil3[ID]

),

"Hours",

CALCULATE (

SUM ( Feuil3[Hours] )

)

),

[Hours] >= 7

)

VAR step2 =

COUNTROWS ( _step1 )

RETURN

step2

[DanT1994]

@Anonymous  thank you for your response, I've included that in my actual data, I think it's almost there - but it isn't kicking out the correct figures.

For example - I know that Dec 21 there were around 190 employees working over 60 hours, but the formula is kicking out a much lower figure(53)  See results below:

If it helps. my source data (shift export) looks like this: