Average of measure with Calculate

[Achamorroman]

Dear all,

I have this expression: 

TAM Amount = CALCULATE(    
         SUM(CifSales[Amount]);
         DATESINPERIOD(CalendarTable[DateKey];LASTDATE(CalendarTable[DateKey]);-12;MONTH)
         )

This, calculate the TAM amount of my sales table (reference date is november 2017). The result its as follows:

TAM AmountWith this, I need to obtain the average value of those 12 records. The correct amount is 11,834,775.87

I have tried several forms of Average and none of them works for me. ¿Can you please help me?

Thanks in advance!

Best regards,

Antonio.

[v-chuncz-msft]

@Achamorroman,

You may use a similar way as shown here.

[mattbrice · Accepted solution]

AVERAGEX ( SUMMARIZE( CalendarTable;'CalendarTable'[Year]; CalendarTable[ShortMonthName] ); [TAM Amount] )

[MFelix]

Hi @Achamorroman,

You need to use an AveregeX expression add another measure like this:

Average on total =
IF (
    HASONEVALUE ( Calendar[Years] );
    [TAM Amount];
    AVERAGEX ( ALL ( 'Calendar'[Year]; Calendar[ShortMonthName] ); [TAM Amount] )
)

Regards,

MFelix

[Achamorroman]

Hi @MFelix, thanks for fast reply!

Unfortunately, that formula doesn't fit what I need. This is the result:

The line result is the same, but total doesn't work. 

Thanks again for your help!

Regards

A.Chamorro 

[Achamorroman]

Hi again,

I found this great article about Rolling 12 Months Average in DAX. I'm going to test it now, it seems to be a solution!

Regards!

A.Chamorro. 

[Ashish_Mathur]

Hi @Achamorroman,

What result are you expecting?

[Achamorroman]

Hi @Ashish_Mathur,

I need one graphic as follows:

• Always 12 months before selected month and year on a slicer.
• Each month represent the Moving annual totals (MAT).
• Total of the matrix should be the sum of that 12 months divided by 12

From now, thanks to @mattbrice (its formula works!) I have the right total, but I don't know how can I obtain always those 12 months having a slicer as reference.

Any help would be very appreciated

Thanks!