Calculate sum(x) months back in a matrix

[wvanpeel]

I would like  to achieve the following in a matrix summing order qty totals:

Period 1:   100

Period 1;   total of (last 6 periods) including period 1

Period 2:   60

Period 2;   total of (last 6 periods)

Periods_vw = a date table. 

Orders = order qty table

This formula gives me an error:

Qty last 6 periods = CALCULATE(sum([qty]);FILTER(Periods_VW;DATESBETWEEN(Periods_VW[PeriodDate];DATEADD(Periods_VW[PeriodDate];-6;MONTH);ENDOFMONTH(Periods_VW[PeriodDate]))))

mdxScript(Model) (3,19) Calculation error in measure:  Orders[Qty last 6 periods]: a table of multiple values was supplied where a single value was expected

[v-yuta-msft · Accepted solution]

Hi wvanpeel,

Based on your description, you want to sum up qty in last 6month row by row, right?

To achieve your requirement, please create a measure [Qty last 6 periods] and refer to dax formula below:

Qty last 6 periods =

CALCULATE (

    SUM ( [qty] ),

    DATESBETWEEN (

        Periods_VW[PeriodDate],

        EDATE ( MAX ( Periods_VW[PeriodDate] ), -6 ),

        ENDOFMONTH ( Periods_VW[PeriodDate] )

    )

)

The result is like below and you can refer to PBIX:https://www.dropbox.com/s/y1f3n1c5uj6iwhb/for%20wvanpeel.pbix?dl=0

Best Regards,

Jimmy Tao

[wvanpeel]

Many thx Jimmy this works as a train.

gr

Wim