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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors