cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dom_Bal_1987 Regular Visitor
Regular Visitor

Sum of average - Additional assistance

Hi,

i'd create a table like this in DAX.

  • In the 1st step there is the start data (in yellow).
  • In the 2nd step there is an average for 3 values ( in 3 months - row 7)

Which is the DAX formula to do this ? Is it possible ?   problem.jpg

2 REPLIES 2
TeigeGao New Contributor
New Contributor

Re: Sum of average - Additional assistance

Hi Dom_Bal_1987,

According to your description, my understanding is that you want to get the average of the data from current month to later two months.

Could you please tell us what’s your original table like? Using the Date as the column name? In this scenario, we need to unpivot the table:

1111.png

The result will like below, change the column names to Date, MM - ManMonths:

222.png

Then create a calculated column like below to create an index:

Row Number = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[Date] < EARLIER(Table1[Date])))+1

After that we can calculate the MM - ManMonths (Media - I Round) using the following query:

MM - ManMonths (Media - I Round) = CALCULATE(AVERAGE(Table1[MM - ManMonths]),FILTER(ALL(Table1),Table1[Row Number] >= EARLIER(Table1[Row Number]) && Table1[Row Number] <= EARLIER(Table1[Row Number]) +2 ))

The result will like below:

2323.png

Best Regards,

Teige

 

Dom_Bal_1987 Regular Visitor
Regular Visitor

Re: Sum of average - Additional assistance

Hi, 

thanks for your solution. I forgot that the excel table is the sum of many rows. Your solution it's good but i've to change the index step. 
This is the situation: i have many rows to sum and i'd i have to do the same work on these rows. 

How can i insert the index coloumn for each date, not row? I can use the allexcept formula for each date to insert the index coloumn ? 

 

Cattura.PNG