cancel
Showing results for
Did you mean:

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

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

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 give back to the community!
Thank You!

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors