Forum Discussion

esesmutuesi's avatar
esesmutuesi
New Member
3 years ago

Totals for future dates

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!
Totals

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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]) ))