Calculate MAT (Moving Anual Total) only when I have a complete MAT (12 months)

[Patekos]

I am trying to calculate MAT Moving Anual Total only when I have 12 months of sales.
how can I restrict the calculation of the MAT only for a complete MAT.

My current measure formula is:

MAT =

VAR Current_Date= MAX('Aux_Date'[End of Month Date])

VAR Previous_Date= DATE(YEAR(Current_Date),MONTH(Current_Date)-12,DAY(Current_Date))

VAR Result= CALCULATE(
SUM(Vendas_Base_Data_Final[Unidades]),
FILTER(
Vendas_Base_Data_Final,
Vendas_Base_Data_Final[End of Month Date] > Previous_Date && Vendas_Base_Data_Final[End of Month Date] <= Current_Date
             )
)

VAR Aux_N_Months= CALCULATE(
COUNTROWS ( VALUES ( Vendas_Base_Data_Final[End of Month Date])),
FILTER(
Vendas_Base_Data_Final,
Vendas_Base_Data_Final[End of Month Date] > Previous_Date && Vendas_Base_Data_Final[End of Month Date] <= Current_Date
             )
)

Return
if (SUMMARIZE(Aux_N_Months,[Aux_N_Months])=12, Result,BLANK())

But this is not correct. Anyone can help?

[MattAllington · Accepted solution]

I assume the date in which 12 months commenced is a fixed point in time. The simplest and most efficient solution is to just hard code it inside an if. 

IF(max(fact[date]]>date(2018,3,3), :your code here: )

also, do yourself a favour and simplify your table names 

instead of Vendas_Base_Data_Final

why not Data?

[v-kkf-msft · Accepted solution]

Hi @Patekos ,

I create the following sample data to calculate MAT Moving Anual Total only when I have 3 months of sales. . Then create measure:

MAT = 
VAR Current_Date= 
  MAX('Aux_Date'[End of Month Date])
VAR Previous_Date= 
  EOMONTH(Current_Date,-3)
VAR Result= 
  CALCULATE(
    SUM(Vendas_Base_Data_Final[Unidades]),
    FILTER(
      Vendas_Base_Data_Final,
      Vendas_Base_Data_Final[End of Month Date] > Previous_Date 
      && Vendas_Base_Data_Final[End of Month Date] <= Current_Date
    )
  )
VAR Aux_N_Months= 
  CALCULATE(
    COUNTROWS ( Vendas_Base_Data_Final ),
    FILTER(
      ALL(Vendas_Base_Data_Final),
      Vendas_Base_Data_Final[End of Month Date] > Previous_Date 
      && Vendas_Base_Data_Final[End of Month Date] <= Current_Date
    )
  )
Return
  if (Aux_N_Months=3, Result,BLANK())

This is my PBIX file.

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-lazhang_microsoft_com/Efd-oV7r1rFAuzmmyMXVe9...

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[MattAllington · Accepted solution]

I assume the date in which 12 months commenced is a fixed point in time. The simplest and most efficient solution is to just hard code it inside an if. 

IF(max(fact[date]]>date(2018,3,3), :your code here: )

also, do yourself a favour and simplify your table names 

instead of Vendas_Base_Data_Final

why not Data?