Calculate averages by day of the week

[Anonymous]

I need to create a bar chart with average Sales by Product based on the filter I choose by each day of the week.

For example.. if I select Monday in the Dates tables filter, the chart should show the averages of app counts submitted on only Mondays of that month/year/ time period selected in time slicer and similarly for other days of the week.

I would really appreciate if someone can help.

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you can refer to below steps:

1.I have entered some sample data to test for your problem. Now you can create a calculated column to calculate the weekday.

weekday = WEEKDAY([Date],1)

2.Create a Clustered column chart value and add the [Year], [Month], [Day], and [Value] fields and modify the [Value] field from "Sum" to "Average".

3.Create a Slicer visual and add the [weekday] field.

Now you can filter your data manually.

You can also download the PBIX file to have a view. If it doesn’t meet your requirements, please provide some dummy data and clarify the requirement.

https://www.dropbox.com/s/90z48xgpmb5hz12/Calculate%20averages%20by%20day%20of%20the%20week.pbix?dl=0

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you can refer to below steps:

1.I have entered some sample data to test for your problem. Now you can create a calculated column to calculate the weekday.

weekday = WEEKDAY([Date],1)

2.Create a Clustered column chart value and add the [Year], [Month], [Day], and [Value] fields and modify the [Value] field from "Sum" to "Average".

3.Create a Slicer visual and add the [weekday] field.

Now you can filter your data manually.

You can also download the PBIX file to have a view. If it doesn’t meet your requirements, please provide some dummy data and clarify the requirement.

https://www.dropbox.com/s/90z48xgpmb5hz12/Calculate%20averages%20by%20day%20of%20the%20week.pbix?dl=0

Regards,

Daniel He

[Anonymous]

@v-danhe-msft Thank you for sharing the solution. This was really helpful.

Now with this bar chart, I also need to include another bar of Moving Average of last 'n' number of Mondays/Tuesdays based on the Weekday selected in the filter. Basically, I have to compare the Avg number of Sales with the Moving Average of Sales by that day for a time frame.

Below is the DAX formula I use to calculate the Moving Average of last 30 days:

Apps 30D Moving Average= AVERAGEX(
DATESINPERIOD(
Dates[DateKey],
LASTDATE(Dates[DateKey]),
-30,DAY),
[Apps])

But, how to implement it for last 30 Mondays/Tuesdays etc. based on the Weekday slicers selected ? I would really appreciate if we could resolve this.

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my previous test pbix file, you can refer to below steps:

1.Create a new measure to calculate the moving average of last 30 days.

Apps 30D Moving Average = CALCULATE(AVERAGE(Table1[Value]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-30,DAY))

2.Create a Clustered column chart and add the [Date] and [Apps 30D Moving Average] field.

3.Use the “weekday slicer” , you can filter last 30 Mondays/Tuesdays data.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/2j2lwomfij0x9re/Calculate%20averages%20by%20day%20of%20the%20week2.pbix?dl=0

Regards,

Daniel He

[Anonymous]

@v-danhe-msft 

This is great!!

Thank you so much for your quick response.