Calculating Average Sales Per Day by Month / Week

[win_anthony]

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

Your support is greatly appreciated! 

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] )
)

[Anonymous · Accepted solution]

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.

[fhill]

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 )

))