cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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!

1 ACCEPTED SOLUTION
Community Support

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 2
Community Support

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

Super User

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.