- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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. 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 🙂
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
07-31-2024 07:28 AM | |||
05-03-2024 03:14 AM | |||
07-06-2024 07:45 AM | |||
05-01-2024 04:22 AM | |||
12-02-2023 05:04 AM |
User | Count |
---|---|
123 | |
79 | |
59 | |
58 | |
44 |
User | Count |
---|---|
179 | |
120 | |
82 | |
70 | |
53 |