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.
Hi All,
Column 1 is week number and Column 2 is the total sales per week.
I am trying to created Column 3 , which is average of future 4 weeks , including the current week value.
Thank you very much
Solved! Go to Solution.
@Fowmy @nandukrishnavs Thank you very much for support and help here.
Final measure looks like as below and generates the desired result as shared in my original post.
@Anonymous
Great!
You can now accept the reply as a solution and click on the Thumbs-Sup icon if you like it.
Thanks
Fowmy
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Add this as a new column:
Future Avg Forecast Sales =
VAR _CURRENTWEEK = [Week No]
VAR _DATE = FILTER(
'Table 2',
'Table 2'[Week No]>= _CURRENTWEEK && 'Table 2'[Week No] <= _CURRENTWEEK + 3
)
RETURN
IF(
COUNTROWS(_DATE)<4,BLANK(),
AVERAGEX(
_DATE,
'Table 2'[Weekly_Forecast]
)
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy At column level, it gives me average of all the Product in my table.
How can I filter based on the Product --> Week --> AVERAGEX
Is it also dependednt on the Data Model ?
I tried the following, using ALLSELECTED ?
Future Avg Forecast Sales =
VAR _CURRENTWEEK = [Week No]
VAR _DATE = FILTER(
ALLSELECTED('Table 2', ProductName),
'Table 2'[Week No]>= _CURRENTWEEK && 'Table 2'[Week No] <= _CURRENTWEEK + 3
)
RETURN
IF(
COUNTROWS(_DATE)<4,BLANK(),
AVERAGEX(
_DATE,
'Table 2'[Weekly_Forecast]
)
)
@Anonymous
I am not sure about the issue you are facing now. Can you explain further or share the PBIX file with the expected results.
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy I don't see any option to attached file here, using WeTransfer, I have shared a file in you Email box.
4 week avg : is the desired result in my file.
You can download from here
https://we.tl/t-8JKJ6wr6Rq
Thanks
@Anonymous
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?
_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy Sure. For this purpose , I have shared using WeTransfer, please downlaod the file using below link.
https://we.tl/t-8JKJ6wr6Rq
Regards
@Fowmy I need a Measure, I tried with modification but it did not work.
I made the follwing changes :
@Anonymous
Use below DAX measure. This should work.
FutureAVGMeasure =
VAR __currentweek =
SELECTEDVALUE ( Table1[Week No] )
VAR __result =
AVERAGEX (
FILTER (
ALL ( Table1 ),
Table1[Week No] < __currentweek + 4 && Table1[Week No]>=__currentweek
),
Table1[Weekly Forecast]
)
RETURN
__result
Note: For other's reference, I have corrected my previous reply.
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
@Anonymous
Try:
Avg Measure =
VAR _CURRENTWEEK = SELECTEDVALUE('DATE'[Week No])
VAR _DATE = FILTER(
'DATE',
'DATE'[Week No]= _CURRENTWEEK && 'DATE'[Week No] <= _CURRENTWEEK + 3
)
RETURN
IF(
COUNTROWS(_DATE)<4,BLANK(),
AVERAGEX(
_DATE,
SUM('Table 2'[Weekly_Forecast])
)
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy @nandukrishnavs Thank you very much for support and help here.
Final measure looks like as below and generates the desired result as shared in my original post.
@Anonymous
Great!
You can now accept the reply as a solution and click on the Thumbs-Sup icon if you like it.
Thanks
Fowmy
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
If you are looking for a calculated column, use below DAX.
FutureAVG =
AVERAGEX (
FILTER (
Table1,
Table1[Week No]
< EARLIER ( Table1[Week No] ) + 4
&& Table1[Week No]
>= EARLIER ( Table1[Week No] )
),
Table1[Weekly Forecast]
)
If you are looking for a measure, use below DAX.
FutureAVGMeasure =
VAR __currentweek =
SELECTEDVALUE ( Table1[Week No] )
VAR __result =
AVERAGEX (
FILTER (
ALL ( Table1 ),
Table1[Week No] < __currentweek + 4
&& Table1[Week No] >= __currentweek
),
Table1[Weekly Forecast]
)
RETURN
__result
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
@nandukrishnavs : Thanks for the response, but this is not working for now. Can you please suggest as per below
First row output should be = (24+45+23+67) / 4 = 39.75....i.e Average(w1,w2,w3,w4) , Second row Average(w2,w3,w4,w5).....so on
I will prefer measure.. Thanks
I tired to modify a bit , but not working still :
FutureAVGMeasure =
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |