cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Revon4
New Member

Compare Daily Average Sales with Last 12 Months Overall Average

 

Hello,

 

I need to build a visual (stacked columns or line chart) that shows the average sales by the day of the week.

Also, I need to compare it with the overall average for that specific day.

 

Example: I have DayOfWeek on the Axis and the Average Sales and Overall Average on the Values. When user selects, in the slicer, a year/month (let's say, 2017-August) it will show that they sold an average of X on Tuesdays and for the last 12 months the average for Tuesdays is Y.

 

 

I have a Sales Fact Table with sales by day and a Calendar DimDate Table with a calculated Weekday column.

 

I tried the following approach so far:

Created an Average Sales Measure

 

 

DailyAverageSales = [TotalSales] / DISTINCTCOUNT(DimDate[Date])

 

 

Being [TotalSales] a simple SUM of SalesAmount. I did that instead of AVERAGE because I have some empty dates in the Fact Table since there were no sales in those days and I needed to include them in the calculation (business requirement).

 

Then I created the Overall Average Measure

 

 

OverallDailyAverageSales =
CALCULATE(
            [DailyAverageSales],
            ALLEXCEPT(
                        DimDate,
                        DimDate[DayOfWeek]
            )
)

 

 

Everything working so far. The problem is that this second measure captures the whole DimDate Table (3 years of data)

 

So, my question is: How do I filter this second measure so it captures only the last 12 months of sales considering the last day of actual Sale and also ignoring the filter on the slicer?

 

I tried the following query with no success:

 

OverallDailyAverageSalesL12M =
CALCULATE(
            [DailyAverageSales],
            DATESBETWEEN(
                        DimDate[Date],
                        NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(Sales[Date]))),
                        LASTDATE(Sales[Date])
            )
)

 

 

Can anyone help me showing what I did wrong?

I appreciate your answer in advance.

 

Best Regards,

Rodrigo

 

 

3 REPLIES 3
Phil_Seamark
Microsoft
Microsoft

Hi @Revon4

 

I think this slight variation might be getting close...

 

OverallDailyAverageSalesL12M = CALCULATE(
	[DailyAverageSales] ,
		DATESBETWEEN(
			'DimDate'[Date],
			FIRSTDATE(DATEADD('DimDate'[Date],-11,MONTH)),
			LASTDATE('DimDate'[Date])
			)
			)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark

 

Thank you for your quick response.

I tried this variation but the visual couldn't be loaded.

 

It gave me an error: 

MdxScript(Model) (199, 13) Calculation error in measure 'FactSales'[OverallDailyAverageSalesL12M]: Function 'DATEADD' expects a contiguous selection when the date column comes from a table on the 1-side of a bi-directional relationship.

 

I am new to this, but I believe this is due to the fact (I forgot to mention before) that I have two Date Tables.

 

Since the original model, from data source, comes with a date table linked to the fact table with a DateKey column, instead of the Date itself (the Fact Table does not have a date column), and as the Date Table is missing some dates because they didn't have sales in that period, I created an Auxiliary Date Table using the function CALENDAR and conected them with the Date field to correct the time intelligence issues I was facing. I don't know if I did right but it was the only way I found on the web to do so.

 

This is how the tables are connected – don't worry about the names of the fields, I am translating to make sense to everybody.

 

 Capturar.PNG

 

Since I am new, I am just supposing this is the problem, and giving you more information.

Anyway, do you think It is possible to correct the query to get the information needed in this scenario?

Thanks for replies in advance.

 

Regards,

Rodrigo

Revon4
New Member

 

Hello,

 

I need to build a visual (stacked columns or line chart) that shows the average sales by the day of the week.

Also, I need to compare it with the overall average for that specific day.

 

Example: I have DayOfWeek on the Axis and the Average Sales and Overall Average on the Values. When user selects, in the slicer, a year/month (let's say, 2017-August) it will show that they sold an average of X on Tuesdays and for the last 12 months the average for Tuesdays is Y.

 

 

I have a Sales Fact Table with sales by day and a Calendar DimDate Table with a calculated Weekday column.

 

I tried the following approach so far:

Created an Average Sales Measure

 

 

DailyAverageSales = [TotalSales] / DISTINCTCOUNT(DimDate[Date])

 

 

Being [TotalSales] a simple SUM of SalesAmount. I did that instead of AVERAGE because I have some empty dates in the Fact Table since there were no sales in those days and I needed to include them in the calculation (business requirement).

 

Then I created the Overall Average Measure

 

 

OverallDailyAverageSales =
CALCULATE(
            [DailyAverageSales],
            ALLEXCEPT(
                        DimDate,
                        DimDate[DayOfWeek]
            )
)

 

 

Everything working so far. The problem is that this second measure captures the whole DimDate Table (3 years of data)

 

So, my question is: How do I filter this second measure so it captures only the last 12 months of sales considering the last day of actual Sale and also ignoring the filter on the slicer?

 

I tried the following query with no success:

 

OverallDailyAverageSalesL12M =
CALCULATE(
            [DailyAverageSales],
            DATESBETWEEN(
                        DimDate[Date],
                        NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(Sales[Date]))),
                        LASTDATE(Sales[Date])
            )
)

 

 

Can anyone help me showing what I did wrong?

I appreciate your answer in advance.

 

Best Regards,

Rodrigo

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.