Incorrect Totals when adjusting the value based on Year-Mo

[Roseventura]

Hi all,

I'm having a very common problem but can't get the correct results.  Basically, I need to adjust my credits if the month is the current month.  If it's not the current month, then don't adjust.  Instead, take the actual credits.

So looking below, if the month is the current month (June 2025), then adjust my credits (-493,597) by the Sales Adjustment (550,000).  Make the difference negative.  The column "Credits Less Credit Adjustment" shows the correct value for each row, but the total is incorrect.  It should be -950,387.36.  I'm guessing because I'm not forcing the measure to be additive?

Here is my measure:  

Credits Less Credit Adjusment =

 VAR SelYrMo = SELECTEDVALUE(PBI_FSCAPF[YEAR-MO])

 VAR SlsAdjust = [Selected Sales Adjustment]

 RETURN

 sumx(

    VALUES(PBI_FSCAPF[YEAR-MO]),

    calculate(

        if( SelYrMo <> [Current Year-Mo],

            [QTD Actual Credit Sales],

            -(SlsAdjust + [QTD Actual Credit Sales]

            )

        )

    )

)

Selected Sales Adjustment = SELECTEDVALUE('Credit Adjustments'[Sales Adjustment])

The credit adjustment is a simple table that stores adjustment values by month and date (joined with my date table).

Any help would be greatly appreciate.

Thank you.

[Ashish_Excel]

Hi,

Drag this measure

=SUMX(VALUES(Calendar[Year-Mo]),[Credits less credit adjustment])

Hope this helps.

[hnam_2006 · Accepted solution]

Hi @Roseventura 

In DAX, when you use SUMX(VALUES...), the calculation is done row by row... even for totals. thats why you get wrong values.

Try the following DAX... it should fix your problem

Credits Less Credit Adjustment =
SUMX (
    ADDCOLUMNS (
        VALUES ( PBI_FSCAPF[YEAR-MO] ),
        "AdjustedCredit",
            IF (
                [YEAR-MO] = [Current Year-Mo],
                -([Selected Sales Adjustment] + [QTD Actual Credit Sales]),
                [QTD Actual Credit Sales]
            )
    ),
    [AdjustedCredit]
)

Best Regards,

hnam_2006

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Roseventura]

Thank you so much!  That did it!

[SamsonTruong]

Hi @Roseventura,

Can you please try the adjusted measure for "Credits Less Credit Adjustment" and let me know if this achieves your desired result:

Credits Less Credit Adjustment = 
VAR CurrentMonth = [Current Year-Mo]
RETURN
SUMX(
    VALUES(PBI_FSCAPF[YEAR-MO]),
    VAR ThisYrMo = PBI_FSCAPF[YEAR-MO]
    VAR Credit = CALCULATE([QTD Actual Credit Sales])
    VAR Adj = CALCULATE([Selected Sales Adjustment])
    RETURN
        IF(
            ThisYrMo = CurrentMonth,
            Credit - Adj,
            Credit
        )
)

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

Connect with me on LinkedIn

Check out my Blog

Going to the European Microsoft Fabric Community Conference? Check out my Session