Progressive Average

[Anonymous]

Hi,
I would like to reproduce this table, made in excel, in DAX.

ID	DATE_PERIOD	MP_PLAN_DELTA	Average
1	27.10.19	3	=AVERAGE(C2;C4;C6)
2	27.10.19	5	=AVERAGE(C3;C5;C7)
1	28.11.19	6	=AVERAGE(C4;C6;C8)
2	28.11.19	8	=AVERAGE(C5;C7;C9)
1	29.12.19	7	=AVERAGE(C6;C8;C10)
2	29.12.19	5	=AVERAGE(C7;C9;C11)
1	30.01.19	6	…
2	30.01.19	8	…
1	01.02.19	5	..
2	01.02.19	7	..
1	02.03.19	3	…
2	02.03.19	6	…

The aim is to obtain the average value over the 3 months and progressively continue.

I'm trying to use this command:

CALCULATE(SUM(COPR_DETAIL[MP_PLAN_DELTA]);DATESINPERIOD(COPR_DETAIL[DATE_PERIOD].[Month];FIRSTDATE(COPR_DETAIL[DATE_PERIOD].[Month]); 3; MONTH))

It's not good. 

do you have any ideas ?

Marco

[v-yuta-msft]

@Anonymous ,

To be general, you may modify the measure like pattern below:

Result =
VAR Current_Month =
    MONTH ( COPR_DETAIL[DATE_PERIOD] )
VAR Previous_Month = Current_Month - 3
RETURN
    CALCULATE (
        SUM ( COPR_DETAIL[MP_PLAN_DELTA] );
        FILTER (
            COPR_DETAIL;
            COPR_DETAIL[DATE_PERIOD] <= Current_Month
                && COPR_DETAIL[DATE_PERIOD] >= Previous_Month
        )
    )

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.

[Anonymous · Accepted solution]

Hi,
Thank you for your answer. The problem I am facing is to do the spread over the three months.
Now I'm trying to use this formula:

MM - ManMonths (Media - I Round)_MM = CALCULATE(AVERAGE(MM[MP_PLAN_DELTA_MM]);DATESINPERIOD(MM[DATE_PERIOD].[Date];MM[DATE_PERIOD]; 2;MONTH))

It seems to be fine in the total picture. As soon as I try to use some filters, it no longer makes sense.
Can I send you the file so you can actually see what my problem is?

Dom

---

[v-yuta-msft]

@Anonymous ,

---

@Anonymous wrote:

Hi,
Thank you for your answer. The problem I am facing is to do the spread over the three months.
Now I'm trying to use this formula:

MM - ManMonths (Media - I Round)_MM = CALCULATE(AVERAGE(MM[MP_PLAN_DELTA_MM]);DATESINPERIOD(MM[DATE_PERIOD].[Date];MM[DATE_PERIOD]; 2;MONTH))

It seems to be fine in the total picture. As soon as I try to use some filters, it no longer makes sense.
Can I send you the file so you can actually see what my problem is?

Dom

---

---

Which field does the filter apply on? To be general, you can use function ALLSELECTED() to apply the filters in your measure. You can upload the sample file to onedrive.

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.

[Anonymous]

Hi,
I solved the problem with the DATESINPERIOD formula I used 60 days instead of 2 months.

I don't know the problem but it is the solution.