Total column matrix Incorrect

[hilary_tesillo]

Hi.

I have the following matrix with the measures Sales and Goal by month; I have a third measure called Sales*, this shows the value of Sales if the month is not closed yet or shows the value of Goal otherwise; the dax expression for this measure is shown in the second image. The problem is that the Total show in the matrix is not correct given the values in every column.

Thank you in advance for your help.

[tamerj1 · Accepted solution]

Hi @hilary_tesillo 

please try

Sales* =
SUMX (
    VALUES ( 'Calendario Venta'[Month Name] ),
    CALCULATE (
        VAR reportingDate =
            MAX ( 'BC Ventas'[Fecha Exp] )
        VAR endMonth =
            EOMONTH ( reportingDate, 0 )
        RETURN
            IF (
                OR ( reportingDate < endMonth, ISBLANK ( reportingDate ) ),
                SUMX ( VALUES ( 'Calendario Venta'[Date (bins)] ), Medidas[Goal] ),
                SUMX ( VALUES ( 'Calendario Venta'[Date (bins)] ), [Sales] )
            )
    )
)