Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

max date table value

Hi,   I feel there is something basic that I'm overlooking.   I have a date table and another table that lists sales transactions for each week (Monday to Sunday).   In the sales transaction co...
  • Anonymous's avatar
    Anonymous
    2 years ago

    I managed to find the answer here: DAX Tables – Calendar and Time – Ville Gullstrands blogg (villezekeviking.com)

     

    Using the following measures for last week, the week before & previous 8 weeks:

     
    Sessions_LW = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-1) 
    Sessions_2W = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-2)
     
    Sessions_8W =
    var _wk1 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-9)
    var _wk2 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-2)
    var _wk3 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-3)
    var _wk4 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-4)
    var _wk5 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-5)
    var _wk6 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-6)
    var _wk7 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-7)
    var _wk8 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-8)

    return  (_wk1 + _wk2 + _wk3 +_wk4 +_wk5 + _wk6 + _wk7 + _wk8)

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

I managed to find the answer here: DAX Tables – Calendar and Time – Ville Gullstrands blogg (villezekeviking.com)

 

Using the following measures for last week, the week before & previous 8 weeks:

 
Sessions_LW = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-1) 
Sessions_2W = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-2)
 
Sessions_8W =
var _wk1 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-9)
var _wk2 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-2)
var _wk3 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-3)
var _wk4 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-4)
var _wk5 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-5)
var _wk6 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-6)
var _wk7 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-7)
var _wk8 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-8)

return  (_wk1 + _wk2 + _wk3 +_wk4 +_wk5 + _wk6 + _wk7 + _wk8)