Forum Discussion
Difference between cumulative values
apatwal , You need have column like these in you date tbale, along with year and week
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
Then you can have week measures like
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
or
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -53)))
or
This week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last year same week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))
Hi amitchandak
Thanks for your reply!
Just to inform we don't have separate date table, date column is included in our dataset and we have millions of records in our dataset.
apatwal , Try like
example
week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Sales'[Date],-364,DAY))
or
This week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Year]=max('Table'[Year]) && 'Table'[Week] = Max('Table'[Week]) ))
Last year same week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Year]=max('Table'[Year])-1 && 'Table'[Week] = Max('Table'[Week])))But My advice would be to get a date table added to the model. Things would be easy