cancel
Showing results for
Search instead 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(
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?

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.

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?

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

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

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

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group #### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings. #### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (13,532)