4 Week Average Sales from Non-Consecutive Days

[revstockli]

I need to caluculate the average sales but I keep getting errors about non-consecutive dates. 

The formula is:  Units Inventory / 4 Week Average Sales Units.  In this case, it would be 825/Aveage of (36,984+35,928+36,179+36,955).  This calculation would work for each date listed for a historical trend.

Thanks in adavce to anyone who can help!

[v-yangliu-msft · Accepted solution]

Hi  @revstockli ,

Here are the steps you can follow：

1. Create calculated column.

Week =
 WEEKNUM('Table'[Date])

Rank =
RANKX(
    FILTER(ALL('Table'),
    YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))),[Week],,ASC)

Mod =
var _mod=
MOD('Table'[Rank],4)
return
IF(
    _mod =0 ,1,0)

Group =
SUMX(
    FILTER(ALL('Table'),'Table'[Date]>=EARLIER('Table'[Date])),[Mod])

2. Create measure.

Measure =
var _sumgroup=
SUMX(
    FILTER(ALL('Table'),
    'Table'[Group]=MAX('Table'[Group])),[Units Inventory])
var _sumvalue=
MAX('Table'[Net Unit Sales])
return
DIVIDE(
    _sumvalue,_sumgroup)

3. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@revstockli , Create date table with week rank column

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

These measures can help

Orders is measure in my example

Avg Last 4 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), [orders]) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

more

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))