Moving average of 3 months in Matrix table

[harib]

Hello Guys,

I need a output like below 1st  image . I have tried but it's coming like 2nd image.  ERI fields are in Columns ,but i need it in rows wise like 1st image with caluclated/measures (ERI Rate , ERI 3MRA Rate, EMI target) fields.

1.

2. 

Sample file.

https://drive.google.com/file/d/1FiLrvioW5i6w26XXEVclxGu4m4GrZIl7/view?usp=sharing

Formulas which i have used in powerbi using dax (Mesures). 

ERI Rate :  IF([ERI_Ship_Qtys]= 0,0,12 * DIVIDE([ERI_Return_Qtys],[ERI_Ship_Qtys]))

ERI Target : IF([ERI_Ship_Qtys]=0,0,12 * DIVIDE([ERI_Target_Failures],[ERI_Ship_Qtys]))

I'm not able to get ERI 3MRA Rate in powerbi

Below formulas are used in SAP BI.

ERI 3MRA Rate: If [Report Month]<[3MAverageStart] Then 0 Else [ERI Average Rate 3 Months]*100

• 3MAverageStart =FormatDate(RelativeDate(ToDate(Min([Report Month]) In Report;"yyyy-MM");2;MonthPeriod);"yyyy-MM")
• ERI Average Rate 3 Months= =If [Report Month] < [3MAverageStart] Then 0 Else
  ((RunningSum([ERI Rate]) - RunningSum(Previous([ERI Rate]; 3))) / 3)+0

Can anyone help in this. I struck here.

Thanks in advance 

[v-yuta-msft]

@harib ,

I'm afraid you can only drag the measures to value field in a matrix visual, you can't drag measures to row field in a matrix visual.

 

Community Support Team _ Jimmy Tao

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

[harib]

@v-yuta-msft 

In this case Normal table visual is better to show the values as i expected like 1st image. And can u make a DAX formula for below syntax. If you can provide DAX formula it will be a great helpfull for me. 

ERI 3MRA Rate = If [Report Month]<[3MAverageStart] Then 0 Else [ERI Average Rate 3 Months]*100

once we find below 2 syntaxs we will get ERI 3MRA Rate.  

• 3MAverageStart =FormatDate(RelativeDate(ToDate(Min([Report Month]) In Report;"yyyy-MM");2;MonthPeriod);"yyyy-MM")
• ERI Average Rate 3 Months= =If [Report Month] < [3MAverageStart] Then 0 Else
  ((RunningSum([ERI Rate]) - RunningSum(Previous([ERI Rate]; 3))) / 3)+0

Thanks in advacne 

[harib]

Any updates ? pls