i'd create a table like this in DAX.
Which is the DAX formula to do this ? Is it possible ?
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:
The result will like below, change the column names to Date, MM - ManMonths:
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:
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 ?