Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabcon_Europe_Social_Bogo

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.