Forum Discussion

mssqlsage's avatar
mssqlsage
Regular Visitor
5 years ago

Datesbetween behavior

Hi everyone,    I'm attempting to return the sum of  [Demand] as below, but limit it to only 7 weeks worth of values.  I have a slicer on [TheDate], which is set to 10/18/2020.   If I hard code i...
Need Help
amitchandak's avatar
amitchandak
Super User
5 years ago

mssqlsage , Not very clear. But you can create week in your date table and work on week instead of dates

 

new columns in date table 

Start Year = STARTOFYEAR('Date'[Date],"3/31")
WeekDay = WEEKDAY([Date],2) //monday
Start of Week = [Date] -[WeekDay]+1 //monday
FY Year = YEAR('Date'[Start Year]) // use end year
FY Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[FY Year]=EARLIER([FY Year])),'Date'[Start of Week]),[Date],DAY),7)+1

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)

 

measures
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 7 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-7 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
last two weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])-1

 

Also, refer if data is getting clubbed into one row

https://www.youtube.com/watch?v=duMSovyosXE