Forum Discussion
Compare value with average value from last 4 same weekdays
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!
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.
3 Replies
- amitchandak
Super User
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.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 🙂
