Forum Discussion
Week-over-week: Week represents last seven days
I have a Power BI file with dynamic refresh. I want to show week over week increase in sales, but based on the last seven days, compared to the seven days before then. In other words, I can't simply use week number. To do this I'm making the following calcuation:
My Unique_Dates table includes the unique dates from my main dataset. The issue is that I'm getting the same value for thisWeekAmount and lastWeekAmount. Any input would be appreciated.
vminvsky , Assume unique_date is date table, else use data table
Rolling 7
Last Period =
var _max =maxx(Transactions,Transactions[date]) //max date of data collected
var _min = _max -7
var datediff1 = datediff(_min,_max,day)
var _maxX = _max-datediff1
var _minX = _min -datediff1
return
CALCULATE(SUM(Sales[Sales Amount]),filter((date),date[date]<=_max &&date[date]>=_min)) //use all(Date) if neededRolling 7 before 7
Last Period =
var _max =maxx(Transactions,Transactions[date]) //max date of data collected
var _min = _max -7
var _maxX = _max-7
var _minX = _min -7
return
CALCULATE(SUM(Sales[Sales Amount]),filter((date),date[date]<=_maxX &&date[date]>=_minX)) //use all(Date) if needed in filter
1 Reply
- amitchandak
Super User
vminvsky , Assume unique_date is date table, else use data table
Rolling 7
Last Period =
var _max =maxx(Transactions,Transactions[date]) //max date of data collected
var _min = _max -7
var datediff1 = datediff(_min,_max,day)
var _maxX = _max-datediff1
var _minX = _min -datediff1
return
CALCULATE(SUM(Sales[Sales Amount]),filter((date),date[date]<=_max &&date[date]>=_min)) //use all(Date) if neededRolling 7 before 7
Last Period =
var _max =maxx(Transactions,Transactions[date]) //max date of data collected
var _min = _max -7
var _maxX = _max-7
var _minX = _min -7
return
CALCULATE(SUM(Sales[Sales Amount]),filter((date),date[date]<=_maxX &&date[date]>=_minX)) //use all(Date) if needed in filter