Forum Discussion
Compare value with average value from last 4 same weekdays
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.
Super UserTry 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 🙂