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

[redface94]

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?

[devanshi]

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

[redface94]

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

[redface94]

thank you but unfortuntelly the proposed measured returns blank as result

[tamerj1]

@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 

[redface94]

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. 

[tamerj1]

@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]
)

[tamerj1]

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]
)

[redface94]

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])

[tamerj1]

@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
    ),
    gloria_view_all[headcount]
)

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

[redface94]

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:

 

[tamerj1 · Accepted solution]

@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]
)

[redface94]

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