MAX DAX Function giving incorrect sum

[gamer_77768]

Hi,

I have a requirement where I have to multiply an amount by 5 if the current month is the first month of quarter, else multiply by 4. Ex: if the month is January(1), April(4), July(7), October(10) then multiply by 5, else multiply by 4 for the rest of the months.

I have written below DAX formula for it:

This gives proper result in most cases where all the months are either start of the month (any combination of 1, 4, 7 ,10) or the other months (any combination of 2, 3, 5, 6, 8, 9, 11, 12).

But gives incorrect result when the months selected are a combination of start of quarter months and other months (ex: 10 and 11).

Below is the snapshot of the result:

This example should sum to 8,930,815.3 but currently it shows 7,948,491.08.

Kindly advise what can be done to achieve the correct results, or if anything is not right in the applied DAX formula.

Thanks in advance!

[Greg_Deckler]

@gamer_77768 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[BrunoTalini · Accepted solution]

Hello! I would try the following:

RETURN

SUMX( 'Calendar Master',
IF( MAX( 'Calendar Master'[Fiscal month num])IN(1,4,7,10), DIVWEEKS*5, DIVWEEKS*4)
)

If that doesn't work, I'd try checking the filters applied to the sheets and visuals.
Best regards!