Ave. Weekly Sales (6wks)

[Reddyp]

Need help in DAX formula for Ave. Weekly Sales (6wks)

I have "date column" and "last week sales"  and "quantity"

[v-kkf-msft · Accepted solution]

Hi @Reddyp ,

Please try the measure.

Ave. Weekly Sales = 
VAR startdate = MAX ( 'Calendar'[Date] ) - 6 - 5 * 7
VAR enddate = MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[last week sales] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Date] >= startdate
                && 'Calendar'[Date] <= enddate
        )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Reddyp , You need to have columns like these in you 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

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

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8