Showing Daily Totals with comparison to 4-week average

[MReinagl]

Hi,

I have a table with transactional sales and want to show the daily total along with a weekday-average.

So for example the totals for each day of last week Monday July 5th - Sunday July 11th. But then I want to compare the value for Monday July 5th with the average of the four Mondays before, i.e. June 28th, 21st, 14th, and 7th. The same then for Tue, Wed,...

Anybody any hints?

Thanks

Matthias

[v-jayw-msft]

Hi @MReinagl ,

You could create a calculated column use weeknum() function to get the week number. Then use allexcept() function to get the total sales for eache week.

https://docs.microsoft.com/en-us/dax/weeknum-function-dax 

https://docs.microsoft.com/en-us/dax/allexcept-function-dax 

Best Regards,

Jay

[MReinagl]

Hi @v-jayw-msft ,

Thanks for your reply.

I'm afraid I don't understand your proposal, but probably I was not clear enough in my question.

I want to show the daily sales in the first row, and the average for each weekday in the second. So the table should look like this:

But I struggle on how to create a measure which shows the average sales for each weekday, i.e. the second row.

Regards

M.

[v-jayw-msft]

Hi @MReinagl ,

Not sure how you get the result. It's better to share some sample data.

Best Regards,

Jay

[MReinagl]

Unfortunately I am not able to attach a Power BI or Excel file here. Hence let's try this:

Date	Daily Total Sales
19. May	8.543
20. May	8.308
21. May	9.595
22. May	10.960
23. May	7.346
24. May	6.741
25. May	6.573
26. May	11.013
27. May	8.320
28. May	10.864
29. May	12.168
30. May	6.698

What i want to get is this:

	24. May	25. May	26. May	27. May	28. May	29. May	30. May
	Mon	Tue	Wed	Thu	Fri	Sat	Sun
Daily Sales	6.741	6.573	11.013	8.320	10.864	12.168	6.698
Weekday Average	6.741	6.573	19.556	8.314	10.229	11.564	7.022

[MReinagl]

I got actually one step further.

Below screenshot now shows what I want to achieve in terms of the numbers:

I achieved this by using a calculated column WeekDay:

WeekDay = WEEKDAY(tblDATA[BuchungsTag],2)

with which I then created this measure:

4WkAvg = CALCULATE(
    SUM(tblDATA[SUMME NETTO])/4,
    ALLEXCEPT(
        tblDATA,
        tblDATA[WeekDay]
    ),
    DATESINPERIOD(Datum[Date],[SelectedDate],-1,MONTH)
)

You can see I cheated a bit with the division by 4 as this assumes I already have four weeks of data - which is not the case for May, hence the differing numbers to my prior post.

The matrix uses WeekDay for the columns. And this is the next problem now: the numbers 1-7 are not really useful in this matrix. I need to show the actual dates. But when I use the according field (BuchungsTag) the matrix doesn't work anymore.

So what's next? 

[MReinagl]

To be more specific, below table shows the problem.Currently I have data from May 19th - May 30th. The table should show one week: May 24 - May 30 with the daily sales in the first rows (as it already does) and the average sales per weekday in the second row. This would be:

• Monday: 6.741
• Tuesday: 6.573
• Wednesday: 9.778 (as the sales on Wednesday May 19th were 8.543)
• Thursday: 8.314 (as the sales on Thursday May 20th were 8.308)
• Friday: 10.230 (Fri May 21st: 9.595)
• Saturday: 11.564 (Sat May 22nd: 10.960)
• Sunday: 7.022 (Sun May 23rd: 7.346)