Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
redface94
Helper I
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?Screenshot 2023-05-31 121217.jpg

 

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

View solution in original post

12 REPLIES 12
devanshi
Helper V
Helper V

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

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
Helper I
Helper I

thank you but unfortuntelly the proposed measured returns blank as result

@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 

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. 

@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
Super User
Super User

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

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

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

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:

b0f0f750-0bb4-447d-b9a3-36ae91a99ad4.jpg

 

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors