Last Cpmpleted 8 Weeks

[Anonymous]

Hi All,
I'm still struggling on how to calculate the Avg/Day for The sum of Last completed 8weeks

. The code works but, when i remove the weeknum & add another attribute it goes blank.  see DAX below. 

[tamerj1 · Accepted solution]

Hi @Anonymous 

please try

Avg/Day Last 8 Weeks =
VAR currentweek =
    WEEKNUM ( TODAY () + 250 )
VAR Firstweek = currentweek - 8
VAR Requireweeks =
    FILTER (
        VALUES ( 'Sales '[Fiscal Week] ),
        'Sales '[Fiscal Week] >= Firstweek
            && 'Sales '[Fiscal Week] < currentweek
    )
RETURN
    SUMX ( Requireweeks, [Unit Sold  CY] / 7 )

[amitchandak]

@Anonymous , need new columns as per need

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

Avg Last 8 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), [orders ]) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

[Anonymous]

@amitchandak  What i need is from the current week, i need last complated 8 weeks Total. Eg. let's say the current week is week 9, i want the total from week 8 to 1. the measure you provided only gives me week 8.