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...
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

Anonymous's avatar
Anonymous
Not applicable
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