Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Need help in DAX formula for Ave. Weekly Sales (6wks)
I have "date column" and "last week sales" and "quantity"
Solved! Go to 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.
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |