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.
Hello - hoping an easy tweak:
Here is my formula -- wish I created it but a long while ago somebody helped me.
It works great for an average of my At Cost Retail within a period of time but I want to get the average for weeks NOT promoted for that period of time. So, need to identify the column in my data called "Promotions"= "Non Promo" for both the numerator and then also the denominator.
I have tried researching for hours...nothing I have tried works for my situation. My data is all weekly data.
11/04/23 | $19,119 | Non Promo |
11/11/23 | $18,586 | Non Promo |
11/18/23 | $17,931 | Non Promo |
11/25/23 | $17,480 | Promo |
12/02/23 | $16,207 | Non Promo |
12/09/23 | $17,875 | Promo |
Thank you in advance!!
Solved! Go to Solution.
@krider71 , if this measure do not have filter a new measure
M1= calculate([At Cost Retai $], filter(Table, Table[Promotions]= "Non Promo" ) )
Then have measure like
Avg = CALCULATE(AverageX(Values('Date'[Week Year]),[M1])
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-72,DAY))
Or have week Rank
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
These measures can help
Avg Last 6 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), [M1]) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
@krider71 , if this measure do not have filter a new measure
M1= calculate([At Cost Retai $], filter(Table, Table[Promotions]= "Non Promo" ) )
Then have measure like
Avg = CALCULATE(AverageX(Values('Date'[Week Year]),[M1])
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-72,DAY))
Or have week Rank
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
These measures can help
Avg Last 6 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), [M1]) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
Thank you i will try this. I have never been able to totally grasp the week rank formulas.
IT WORKED!!! SO SIMPLE
THANK YOU
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 |
---|---|
114 | |
105 | |
78 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
84 | |
70 |