Compare value with average value from last 4 same weekdays

[newbie2181]

Hello,

I have a set of data (columns: day, day_name, value, increase) that looks something like this:

08-Sep	Tuesday	200	13%
09-Sep	Wednesday	150	-6%
10-Sep	Thursday	200	23%
11-Sep	Friday	100	-38%
12-Sep	Saturday	200	23%
13-Sep	Sunday	150	-8%
14-Sep	Monday	120	-26%
15-Sep	Tuesday	180	1%
16-Sep	Wednesday	200	25%
17-Sep	Thursday	180	11%
18-Sep	Friday	200	23%
19-Sep	Saturday	100	-38%
20-Sep	Sunday	200	23%
21-Sep	Monday	150	5%
22-Sep	Tuesday	200	1%
23-Sep	Wednesday	195	22%
24-Sep	Thursday	200	18%
25-Sep	Friday	150	-8%
26-Sep	Saturday	200	23%
27-Sep	Sunday	100	-38%
28-Sep	Monday	140	-2%
29-Sep	Tuesday	190	-4%
30-Sep	Wednesday	200	9%
01-Oct	Thursday	190	12%
02-Oct	Friday	200	23%
03-Oct	Saturday	170	5%
04-Oct	Sunday	200	23%
05-Oct	Monday	140	10%
06-Oct	Tuesday	200	4%

Increase is the percentage increase (or decrease) between the value from a specific day and the average of (the value from 7 days ago, the value from 14 days ago, the value from 21 days ago and the value from 28 days ago). In other words, if looking at the last row (tuesday, 6 oct), the 4% is the percentage increase between the 200 value and the average from the value from last tuesday, 2 tuesdays ago, 3 tuesdays ago and 4 tuesdays ago - the values in red, basically. The formula for Increase is therefore (value - average)/average.

I am having difficulties computing this average and therefore obtaining these percentages in my PBI table (the increase %s from above have been obtained by manual calculation in Excel). Could you give me a hint to get me started?

Thank you for your time!

[amitchandak · Accepted solution]

@newbie2181 ,

Try measures like these with a date table

7 behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,DAy))
14 behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-14,DAy))
21 behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-21,DAy))

diff =
var _cnt = if(isblank([7 behind Sales]),0,1)+if(isblank([14 behind Sales]),0,1)+if(isblank([21 behind Sales]),0,1)
var _last3 = ([7 behind Sales]+[14 behind Sales]+[21 behind Sales]) /_cnt
return
divide([sales] - (_last3 ),_last3)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions



Appreciate your Kudos.

[newbie2181]

Hi! Thank you for your answer. Almost everything worked just fine, I'm now wondering why haven't I thought of creating  measurements for each of the steps in my calculation.

I have an issue though, I was wondering in your suggested 7 behind sales formulae, shoulen't you have "told" power bi somehow to give you the Sales[Sales Amount] value only when Date[Date]=Sales[Date]? In other words, shouldn't this have been something like 

7 behind Sales = CALCULATE(SUM(Sales[Sales Amount]),Sales[Date]=dateadd('Date'[Date],-7,DAy)) instead? (PS: Tried, doesn't work, it gives an error saying DATEADD function can't be used in a True/False expression)

Just to mention, I have created a Date calendar, set it as date and linked 'Date'[Date] to 'Sales'[Date].

Also, another question - why have you used the SUM function inside CALCULATE? I was thinking that at this stage we're only trying to find the value from 7 days ago, not add it to something else. I'm sorry if my questions seem silly, I'm still new to Power BI and still getting my head round it. 🙂

[newbie2181]

Hi! Thank you for your answer. Almost everything worked just fine, I'm now wondering why haven't I thought of creating  measurements for each of the steps in my calculation.

I have an issue though, I was wondering in your suggested 7 behind sales formulae, shoulen't you have "told" power bi somehow to give you the Sales[Sales Amount] value only when Date[Date]=Sales[Date]? In other words, shouldn't this have been something like 

7 behind Sales = CALCULATE(SUM(Sales[Sales Amount]),Sales[Date]=dateadd('Date'[Date],-7,DAy)) instead? (PS: Tried, doesn't work, it gives an error saying DATEADD function can't be used in a True/False expression)

Just to mention, I have created a Date calendar, set it as date and linked 'Date'[Date] to 'Sales'[Date].

Also, another question - why have you used the SUM function inside CALCULATE? I was thinking that at this stage we're only trying to find the value from 7 days ago, not add it to something else. I'm sorry if my questions seem silly, I'm still new to Power BI and still getting my head round it. 🙂

[newbie2181]

Hi! Thank you for your answer. Almost everything worked just fine, I'm now wondering why haven't I thought of creating  measurements for each of the steps in my calculation.

I have an issue though, I was wondering in your suggested 7 behind sales formulae, shoulen't you have "told" power bi somehow to give you the Sales[Sales Amount] value only when Date[Date]=Sales[Date]? In other words, shouldn't this have been something like 7 behind Sales = CALCULATE(SUM(Sales[Sales Amount]),Sales[Date]=dateadd('Date'[Date],-7,DAy)) instead? (PS: Tried, doesn't work, it gives an error saying DATEADD function can't be used in a True/False expression)

Just to mention, I have created a Date calendar, set it as date and linked 'Date'[Date] to 'Sales'[Date].

Also, another question - why have you used the SUM function inside CALCULATE? I was thinking that at this stage we're only trying to find the value from 7 days ago, not add it to something else. I'm sorry if my questions seem silly, I'm still new to Power BI and still getting my head round it 🙂