Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Progressive Average

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

 

IDDATE_PERIODMP_PLAN_DELTAAverage
127.10.193=AVERAGE(C2;C4;C6)
227.10.195=AVERAGE(C3;C5;C7)
128.11.196=AVERAGE(C4;C6;C8)
228.11.198=AVERAGE(C5;C7;C9)
129.12.197=AVERAGE(C6;C8;C10)
229.12.195=AVERAGE(C7;C9;C11)
130.01.196…
230.01.198…
101.02.195..
201.02.197..
102.03.193…
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

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

4 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-yuta-msft
        Community Support
        7 years ago

        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.