Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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??
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
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 🙂
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
User | Count |
---|---|
89 | |
75 | |
69 | |
65 | |
58 |
User | Count |
---|---|
103 | |
94 | |
75 | |
61 | |
58 |