cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors