cancel
Showing results for
Did you mean:
Regular Visitor

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

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(
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?

2 ACCEPTED SOLUTIONS

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.

also, do yourself a favour and simplify your table names

why not Data?

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Community Support

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(
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.

2 REPLIES 2
Community Support

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(
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.

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.

also, do yourself a favour and simplify your table names

why not Data?

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Announcements