Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
win_anthony
Resolver III
Resolver III

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

 

win_anthony_0-1626443512688.png

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

 

win_anthony_1-1626444025367.png

 

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.

View solution in original post

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.
fhill
Resident Rockstar
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 )
))



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Helpful resources

Announcements
Europe Fabric Conference

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.