cancel
Showing results for
Did you mean:
Super User

## Calculating Average Sales Per Day by Month / Week

I have a client that provided a calculation that is to be used to calculate total sales per day. The way that the client came up with the calculation: [Total Sales Per Day] = Total Sales / 90 (the 90 is days to reflect the amounts as shown per fiscal quarter bar chart). Now there is a new requirement that is to show this same calculation reflected by Month + Week. My problem is that the client is requiring that I use their calculation to reflect total sales by Fiscal Quarter + Month + Week on bar charts. Their calculation works when reflected by Fiscal Quarter but does not for Month + Week.

Any advice on how you would structure a daily average of sales by Month + Week? Below you will find the following: Current [Total Sales Per Day] measure + Current Average Daily Sales measure

Current [Total Sales Per Day] measure: This measure is working correctly when it is reflected only for Fiscal Quarters.

IF(
DATEDIFF( MIN( 'Table'[Date] ), TODAY(), MONTH ) < 3,
DIVIDE( [Total Sales], DATEDIFF( MIN( 'Table'[Date] ), TODAY(), DAY ) ),
DIVIDE( [Total Sales], 90 )
)

Current Average Daily Sales measure by Month: I tried to use AVERAGEX and the measure above but it is giving me figures that are completely off.

AVERAGEX(
'Date_Table',
CALCULATE( [Total Sales Per Day] )
)

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Tell the client they should think harder. Clearly, they'd benefit from some basic mathematical and business learning 🙂

What you want to do (and this incorporates what the client wants) is this:

[Daily Average] =
DIVIDE(
[Total Amount],
// Count the days visible
// in the current period of
// time. You have to have a proper
// Date table in the model. Do
// not attempt this on a bad
// model.
COUNTROWS( Dates )
)

// The measure above is totally
// flexible. It accomodates ANY
// period of time.
2 REPLIES 2
Anonymous
Not applicable

Tell the client they should think harder. Clearly, they'd benefit from some basic mathematical and business learning 🙂

What you want to do (and this incorporates what the client wants) is this:

[Daily Average] =
DIVIDE(
[Total Amount],
// Count the days visible
// in the current period of
// time. You have to have a proper
// Date table in the model. Do
// not attempt this on a bad
// model.
COUNTROWS( Dates )
)

// The measure above is totally
// flexible. It accomodates ANY
// period of time.
Resident Rockstar

oh, this is SOOO Bad on so many levels....  But maybe something like this, that will change the logic of the Measure based on teh # of Days displayed?  If it's Quarterly, and there's over 33 days per Column, do X...  Else, if there's more than 8 days per Column (Now down to Monthly) do X, and lastly we are down to the week level, and just divide by 7?

Worse Measure =
IF ( COUNTA(Sales[Date]) > 33,
IF(
DATEDIFF( MIN( 'Sales'[Date] ), TODAY(), MONTH ) < 3,
DIVIDE( [Sales], DATEDIFF( MIN( 'Sales'[Date] ), TODAY(), DAY ) ),
DIVIDE( [Sales], 90 )
) ,
IF ( COUNTA(Sales[Date]) > 8,
IF(
DATEDIFF( MIN( 'Sales'[Date] ), TODAY(), MONTH ) < 1,
DIVIDE( [Sales], DATEDIFF( MIN( 'Sales'[Date] ), TODAY(), DAY ) ),
DIVIDE( [Sales], 30 )
)
,
DIVIDE( [Sales], 7 )
))

Please give Kudos or Mark as a Solution!

Proud to be a Super User helping give back to the community!
Thank You!

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors