DAX for 4 week Run Rate

[Anonymous]

Hi,

Trying to find the right Dax to use to create a 4 week run rate as a measure rather than a calculated column.

Have tried 

4wk RR Qty Sales:=(CALCULATE(SUM(FactSales[Qty Sales]),DATESINPERIOD(DimCalendar[Date],max(DimCalendar[Date]),-28,DAY)))/4

This seems to work however any total of say a month will still give the value of the last week in the month.

Any help??

[v-juanli-msft]

Hi @Anonymous 

Could you show an example expected output?

Let to say, today is 2019/4/3, if "FactSales" only have sales till today, then

"4wk RR Qty Sales" for today should be 

(the sum for 2019/3/7~2019/4/3)/4

there are four weeks from 2019/3/7~2019/4/3

"4wk RR Qty Sales" for 2019/4/2 should be 

(the sum for 2019/3/6~2019/4/2)/4

Is my understanding correct?

Best Regards

Maggie

[Anonymous]

Hi Maggie

That is exactly the output I would be after.

Only addtional consideration would be that the sales data may go past this period but we would want to run a report for the week ending the 2019/4/3.  so it that report we may show the sales for that week and also the 4wk RR as at that weeek.

Hope that makes sense.

Thanks for your help 🙂

[v-juanli-msft]

Hi @Anonymous 

Sorry, i don't understand this correctly.

"Only addtional consideration would be that the sales data may go past this period but we would want to run a report for the week ending the 2019/4/3.  so it that report we may show the sales for that week and also the 4wk RR as at that weeek"

Could you show an example? Is my understanding below right?

For example, in 2019/4/3, "4wk RR Qty Sales" of your formula would calcuate sum/4 for period "2019/3/7~2019/4/3",

But you have data in 2019/4/4, 2019/4/5 too, then you want  new "4wk RR Qty Sales" calculate (the sum for 2019/3/11~2019/4/7)/4?

Best Regards

Maggie