Forum Discussion

trwatts's avatar
trwatts
Helper I
4 years ago

Help with calculation

Hey everyone,    I need to work out how many weeks there are in a month but there are some conditions.  The end goal is to calculate the number of purchases an individual customer makes each week....
Need Help
amitchandak's avatar
amitchandak
Super User
4 years ago

trwatts , You create create a date table

 

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

WeekDay = WEEKDAY([Date],2)

WeekDay flag = if(WEEKDAY([Date],2) < 6,1,0)

 

then measure like

 

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank] && 'Date'[WeekDay flag] =1 )))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay flag] =1 ))

 

4th last week

Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-4 && 'Date'[WeekDay flag] =1 ))

 

Product Type should filter if joined, else we need add code