cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Patekos
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.

View solution in original post

v-kkf-msft
Community Support
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())

v-kkf-msft_0-1614647831648.png

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.

View solution in original post

2 REPLIES 2
v-kkf-msft
Community Support
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())

v-kkf-msft_0-1614647831648.png

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.

View solution in original post

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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.