Power pivot - compare to the same weekday

Question

╔═══════╦═════╦═══════╦════════╦═══════╦════════╗
║ Month ║ Day ║ City1 ║        ║ City2 ║        ║
╠═══════╬═════╬═══════╬════════╬═══════╬════════╣
║       ║     ║ Abs.  ║ Relat. ║ Abs.  ║ Relat. ║
║ Jan   ║ 1   ║ X     ║ 10%    ║ Z     ║ -3%    ║
║       ║ 2   ║ X     ║ -7%    ║ Z     ║ 5%     ║
║       ║ ... ║       ║        ║       ║        ║
║       ║ 31  ║       ║        ║       ║        ║
║ Feb   ║ 1   ║ ...   ║ ...    ║ ...   ║ ...    ║
║       ║ 2   ║       ║        ║       ║        ║
║       ║ ... ║       ║        ║       ║        ║
║       ║ 31  ║       ║        ║       ║        ║
╚═══════╩═════╩═══════╩════════╩═══════╩════════╝

Abs: Absolute; Relat.: relative

I'm new to power pivot.

I need to create a table like this, where Relative column is based on the Average of the last 4 weeks (for the same weekday).

example: January 1st was a Friday, so:

1. Relative column will compute the average for the last 4 fridays (4, 11,18, 25 December)
2. Then compare with the absolute value (expressing in %)

How can I compute this Relative column?

Answer 1

The easiest way to do this is to break it down into two stages. Firstly, create a measure that works out the average over the four preceding weeks:

Average Abs = 0.25 * ( CALCULATE(SUM(Table1[Abs]),DATEADD(Table1[Date],-7, Day)) + CALCULATE(SUM(Table1[Abs]),DATEADD(Table1[Date],-14, Day))+ CALCULATE(SUM(Table1[Abs]),DATEADD(Table1[Date],-21, Day))+ CALCULATE(SUM(Table1[Abs]),DATEADD(Table1[Date],-28, Day)) )

Important: the DateAdd function will only work if there are no gaps in your set of dates. If you have a date with no data, then you either need to create a blank entry in your data table for that date or (recommended option) create a date dictionary table and link your fact table to that.

Note: I have assumed that your data goes back at least 4 weeks before the start of your report, otherwise adding up and dividing by 4 will not give a correct average.

Then create a measure that compares this to the Abs value for this date:

relat = DIVIDE(SUM(Table1[Abs]),[Average Abs])-1

Format this as a %.

Based on some random data I generated, I get something that looks like this: