Reply
newbie2181
New Member
Partially syndicated - Outbound

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-SepTuesday20013%
09-SepWednesday150-6%
10-SepThursday20023%
11-SepFriday100-38%
12-SepSaturday20023%
13-SepSunday150-8%
14-SepMonday120-26%
15-SepTuesday1801%
16-SepWednesday20025%
17-SepThursday18011%
18-SepFriday20023%
19-SepSaturday100-38%
20-SepSunday20023%
21-SepMonday1505%
22-SepTuesday2001%
23-SepWednesday19522%
24-SepThursday20018%
25-SepFriday150-8%
26-SepSaturday20023%
27-SepSunday100-38%
28-SepMonday140-2%
29-SepTuesday190-4%
30-SepWednesday2009%
01-OctThursday19012%
02-OctFriday20023%
03-OctSaturday1705%
04-OctSunday20023%
05-OctMonday14010%
06-OctTuesday2004%

 

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!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Syndicated - Outbound

@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.

Join us as experts from around the world come together to shape the future of data and AI!
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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

Syndicated - Outbound

@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.

Join us as experts from around the world come together to shape the future of data and AI!
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

Syndicated - Outbound
Syndicated - Outbound

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. 🙂

Syndicated - Outbound

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 🙂

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)