Forum Discussion

revstockli's avatar
revstockli
New Member
3 years ago
Solved

4 Week Average Sales from Non-Consecutive Days

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!

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

2 Replies