topic Re: need urgent help with a DAX formula (SUM for a specific month only) in DAX Commands and Tips

need urgent help with a DAX formula (SUM for a specific month only)

redface94 — Wed, 31 May 2023 11:16:51 GMT

I have a table in power BI, where I have to calculate via a measure the sum of the headcount for December only (the year does not matter), filtering 2 atributes also. I have tried the following measure, but it displayes blank as result:

TY Plan HC =

CALCULATE(SUM(gloria_view_all[headcount]),FILTER(gloria_view_all,gloria_view_all[Attribute]="costs"),FILTER(gloria_view_all,gloria_view_all[gloria_value_types]="020",FILTER(gloria_view_all,gloria_view_all[Date]=12))). The format of the date can be seen below:

Any suggestions on how to fix the measure?

Re: need urgent help with a DAX formula (SUM for a specific month only)

tamerj1 — Wed, 31 May 2023 09:26:29 GMT

Hi redface94
Please try

TY Plan HC = SUMX ( FILTER ( ALL ( gloria_view_all ), gloria_view_all[Attribute] = "costs" && gloria_view_all[gloria_value_types] = "020" && gloria_view_all[Date] = 12 ), gloria_view_all[headcount] )

Re: need urgent help with a DAX formula (SUM for a specific month only)

redface94 — Wed, 31 May 2023 10:26:39 GMT

thank you but unfortuntelly the proposed measured returns blank as result

Re: need urgent help with a DAX formula (SUM for a specific month only)

tamerj1 — Wed, 31 May 2023 10:48:48 GMT

redface94
If the dax presented in your question returns a value then it would be extremely surprizing if my dax returns blank!

I took the following from your dax: gloria_view_all[Date] = 12 which I suspect perhaps not correct. Perhaps you mean to say: MONTH ( gloria_view_all[Date] ) = 12

Re: need urgent help with a DAX formula (SUM for a specific month only)

devanshi — Wed, 31 May 2023 11:03:45 GMT

CALCULATE(SUM(gloria_view_all[headcount]),

FILTER(gloria_view_all,

AND(gloria_view_all[Attribute]="costs",

gloria_view_all[gloria_value_types]="020",

MONTH(gloria_view_all[Date]=12)))))

Re: need urgent help with a DAX formula (SUM for a specific month only)

redface94 — Wed, 31 May 2023 11:16:13 GMT

sorry, I have tried so many formulas, I was confused. Yes, you are right, the result of my measure was blank. I tried with a different one and it was showing me only the value from that selected month, not December.

Re: need urgent help with a DAX formula (SUM for a specific month only)

redface94 — Wed, 31 May 2023 11:17:52 GMT

It does not work, I get the following message: "Too many arguments were passed to the AND function. The maximum argument count for the function is 2."

Re: need urgent help with a DAX formula (SUM for a specific month only)

tamerj1 — Wed, 31 May 2023 11:45:54 GMT

redface94
Have you tried this?

TY Plan HC = SUMX ( FILTER ( ALL ( gloria_view_all ), gloria_view_all[Attribute] = "costs" && gloria_view_all[gloria_value_types] = "020" && MONTH ( gloria_view_all[Date] ) = 12 ), gloria_view_all[headcount] )

Re: need urgent help with a DAX formula (SUM for a specific month only)

redface94 — Wed, 31 May 2023 11:58:19 GMT

I've tried the measure, with 1 additonal bracket, because the one above it is not working, and it returnes 10600, instead of 916, which should be correct: TY Plan HC =

SUMX (

FILTER (

ALL ( gloria_view_all ),

gloria_view_all[Attribute] = "costs"

&& gloria_view_all[gloria_value_types] = "020"

&& MONTH(gloria_view_all[Date]=12)

gloria_view_all[headcount])

Re: need urgent help with a DAX formula (SUM for a specific month only)

tamerj1 — Wed, 31 May 2023 12:18:21 GMT

redface94
That was my last measure:

Changing the location of closing bracket to keep the 12 inside is wrong! please provide some screenshots for better understanding.

Re: need urgent help with a DAX formula (SUM for a specific month only)

redface94 — Wed, 31 May 2023 12:35:38 GMT

I think we are close in getting a good result! With the measure above now I can see the total but unfortunatelly it is not distinguishes between the HC type. The measure shows 962 everywhere, which is the total. Normally I have 919 Heads, thereof 1 leased, thereof 45 working students:

Re: need urgent help with a DAX formula (SUM for a specific month only)

tamerj1 — Wed, 31 May 2023 13:13:21 GMT

redface94
Pleaser try

TY Plan HC = SUMX ( FILTER ( CALCULATETABLE ( gloria_view_all, ALL ( loria_view_all[Date] ) ), gloria_view_all[Attribute] = "costs" && gloria_view_all[gloria_value_types] = "020" && MONTH ( gloria_view_all[Date] ) = 12 ), gloria_view_all[headcount] )

Re: need urgent help with a DAX formula (SUM for a specific month only)

redface94 — Wed, 31 May 2023 13:49:54 GMT

it works! thank you so much for all your support, much appreciated!