Totals for future dates

[esesmutuesi]

Hello,

I am lost. I have a measure that calculates the 'expected' sales for future dates. For each date i take the average of last 4 weeks on the same weekday. So for next Monday I will use the sales of last 4 mondays. The measure works fine, but ofcourse as nothing new in DAX the total is not adding up (for the week/month). I have tried everything. Below is an example of measure.

Expected Sales=

var selected_date = SELECTEDVALUE('Dim Date'[Date])

var selected_weekday = SELECTEDVALUE('Dim Date'[WeekDay])

var value_ =

 IF( selected_date >= TODAY(),  CALCULATE( [sales],  FILTER(ALL('Dim Date'), selected_weekday = 'Dim Date'[WeekDay] && 'Dim Date'[Last 4 Weeks] = 1)),   CALCULATE( [Compensation], 'Dim Date'[Date] > selected_date -28 && selected_weekday = 'Dim Date'[WeekDay])) / 4

 return

 SUMX('Dim Date', value_)

Any help is appreciated!

[amitchandak]

@esesmutuesi ,

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

Last 4 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-3 && 'Date'[Week Rank]<=max('Date'[Week Rank]) &&  'Date'[Weekday]=max('Date'[Weekday]) ))