0
╔═══════╦═════╦═══════╦════════╦═══════╦════════╗
║ 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?

blocnt
  • 73
  • 1
  • 8

1 Answers1

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:

enter image description here

Gordon K
  • 824
  • 1
  • 8
  • 19
  • I created a date dictionary table and linked to my "main source" table. I created the measure Average Abs, but when I try to add to VALUES, I get – blocnt Oct 19 '16 at 08:54
  • I get "Function DATEADD only works with contiguous date selections." Right now, my data is from January-October. Can you help me? – blocnt Oct 19 '16 at 08:55
  • The date dictionary must not have any gaps - there must be an entry for every day that falls within your date range and also the 4 weeks before. – Gordon K Oct 19 '16 at 08:58
  • Make sure you change the Average Abs function so that the DATEADD part uses the date column of your date dictionary, not your main source. – Gordon K Oct 19 '16 at 08:59
  • That worked! But now the Average Abs is giving the same results that Absolute is giving. Average Abs is like this "CALCULATE(SUM(Table1[aaa]);DATEADD('Date'[Date];-7; Day))" (with the 4 CALCULATEs). To give you context, Table1 is a table with several Dates for the same day. – blocnt Oct 19 '16 at 09:40
  • Have you used 'Date'[Date] for the rows on your table? – Gordon K Oct 19 '16 at 09:42