cancel
Showing results for
Did you mean:
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?

Best Regards,

Rodrigo

3 REPLIES 3
Microsoft

Hi @Revon4

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

```OverallDailyAverageSalesL12M = CALCULATE(
[DailyAverageSales] ,
DATESBETWEEN(
'DimDate'[Date],
LASTDATE('DimDate'[Date])
)
)```

Proud to be a Datanaut!

New Member

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.

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?

Regards,

Rodrigo

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?

Best Regards,

Rodrigo

Announcements