Running/moving average last 3 months - Wrong measure total with if statement

[PBIler]

Hi everyone,

I am trying to create a measure with a running/moving average of the last 3 months. I used a formula which works and returns  correct totals in Excel/Power Pivot:

NMA UC Average =CALCULATE(sumx(table1;table1[column1]);DATESINPERIOD(Date[Date];LASTDATE(Date[Datum]);-3;MONTH))/3

But as soon as i add an if statement to the calculated column (see formular below), excel/pivot table is calculating the wrong measure total (see screenshot). How can this be solved?

NMA UC Average:=

var
    var_delta = CALCULATE(sumx(table1;table1[column1]);DATESINPERIOD(Date[Date];LASTDATE(Date[Date]);-3;MONTH))/3
return
 if([NMA Unique Clients]<>0;[NMA Unique Clients];var_delta)

Thank you very much for your help!

[PBIler]

Hi everyone,

I am trying to create a measure with a running/moving average of the last 3 months. I used a formula which works and returns  correct totals in Excel/Power Pivot:

NMA UC Average =CALCULATE(sumx(table1;table1[column1]);DATESINPERIOD(Date[Date];LASTDATE(Date[Datum]);-3;MONTH))/3

But as soon as i add an if statement to the calculated column (see formular below), excel/pivot table is calculating the wrong measure total (see screenshot). How can this be solved?

NMA UC Average:=

var
    var_delta = CALCULATE(sumx(table1;table1[column1]);DATESINPERIOD(Date[Date];LASTDATE(Date[Date]);-3;MONTH))/3
return
 if([NMA Unique Clients]<>0;[NMA Unique Clients];var_delta)

Thank you very much for your help!

[v-huizhn-msft]

Hi @PBIler,

Have you resolved your issue? If you have, please mark the right/helpful reply as answer. More people will learn new things here.  If you haven't, please feel free to ask.

Best Regards,
Angelia

[v-huizhn-msft · Accepted solution]

Hi @PBIler,

Please use the formula to create measure and check if it works fine.

NMA UC Average =
VAR var_delta =
    CALCULATE (
        SUMX ( table1, table1[column1] ),
        DATESINPERIOD ( Date[Date], LASTDATE ( Date[Date] ), -3, MONTH )
    )
        / 3
RETURN
    IF (
        COUNTROWS ( VALUES ( Date[Date] ) ) = 1,
        IF ( [NMA Unique Clients] <> 0, [NMA Unique Clients], var_delta ),
        SUMX (
            VALUES ( Date[Date] ),
            IF ( [NMA Unique Clients] <> 0, [NMA Unique Clients], var_delta )
        )
    )

There are two similar thread for your reference.

Dealing with Measure Totals
Matrix Table Total Row not calculating totals

Best Regards,
Angelia