7 day moving average of measure

[CP525]

Hi all,

so I'm struggling to create a 7 day moving average of a measure I created.

The measure is set up as follows:

WE rate =

DIVIDE(

    SUM('factWE'[WE value]),

    SUM('factPO'[PO value]),

    0

) *100

It gives back the percentage of goods receipt by orders. I visualized it to see how the percentage improves over time. There is a huge variation in the data, so I wanted to smooth it with a 7 day moving average, but I'm unable to create the correct DAX formula for that. My main problem is that I can't use the average function as this formula cannot reference to the measure.

Any tips on that?

[v-weiyan1-msft · Accepted solution]

Hi @CP525 ,

Based on your description,
I created some data:

Please try the following steps:
1.Please try code as below to create Calculated Column.

Mod = 
VAR _rank = RANKX(ALL('Table'),'Table'[date],,ASC)
VAR _mod=MOD(_rank,7)
RETURN
IF(_mod=1,
    1,
    0)

Group = CALCULATE(SUM('Table'[Mod]),
                  FILTER(ALL('Table'),'Table'[date]<=EARLIER('Table'[date]))
                )

2. Use the following code to create a Measure.

Measure = 
var _1 = MAX('Table'[Date])
return
AVERAGEX(
    FILTER(
        SUMMARIZE(ALL('Table'),
        'Table'[Date],'Table'[Group],
        "Avg value",AVERAGEX('Table',[Rate])),
        'Table'[Date] <= _1 && 'Table'[Group] = MAX('Table'[Group])
        ),
        [Avg value]
)

Result is as below.

Is this the result you expect?
If I've misunderstood you, please provide detailed sample data and the results you are hoping for. We can better understand the problem and help you. Show it as a screenshot or excel. Please remove any sensitive data in advance.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Corey_M]

can you provide what your tables look like, and if you have a date table with a relationship to factWe and FactPO?

The solution is gonna involve using:

Calculate and

DATESINPERIOD (
        Table[Date],
        LASTDATE ( Table [Date] ),
        -7,
        DAY
    )