cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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?

1 ACCEPTED SOLUTION
Super User

@redface94

``````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
),
)``````
12 REPLIES 12
Helper V

FILTER(gloria_view_all,

AND(gloria_view_all[Attribute]="costs",

gloria_view_all[gloria_value_types]="020",

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

Helper I

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."

Helper I

thank you but unfortuntelly the proposed measured returns blank as result

Super User

@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

Helper I

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.

Super User

@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
),
)``````
Super User

Hi @redface94

``````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
),
)``````
Helper I
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)
),
Super User

@redface94
That was my last measure:

``````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
),
)``````

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

Helper I

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:

Super User

@redface94

``````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
),
)``````
Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.