Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

 

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

 

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
    fhill
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

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