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 guys,
I need to calculate the average considering that: The previous months of 12, has average by the quantity of month. So if I have 6 months, the average is replicated to the previous months, if I have 7,8,9,10,11 and 12 months I have the average replicated to the previous months. If it has 13 months, I need to calculate the moving average by 12 months (this is working).
So, considering the image, I need that AVG2 has the value 0.366333333 to index minors that 13.
Solved! Go to Solution.
@twister8889
Use this measure :
New Avg =
VAR IND = SELECTEDVALUE('Table'[Index])
VAR _12 =
CALCULATE(
AVERAGE('Table'[Value]),
'Table'[Index] <= 12,
ALLSELECTED('Table')
)
VAR _OVER12 =
CALCULATE(
AVERAGE('Table'[Value]),
'Table'[Index] <= IND && 'Table'[Index] > IND - 12
,ALLSELECTED('Table')
)
RETURN
IF( IND > 12,
_OVER12,
_12
)
________________________
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
@twister8889
Can you share some sample data in excel/csv format and the expected result to have a clear understanding of your question?
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
First of all, thank you for your answer, I share my pbi (Page 2) file here: https://1drv.ms/u/s!AoDYwrtLrltJnxlh-8VaZdW7EZVg
@twister8889
Your explanation is no quite clear for me, can you share the expected result for each line, maybe on Excel?
________________________
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
HI @twister8889 ,
You will need to create an index column.
You can create this in Power Query
Post this you can use this measure.
Moving_Average_12_Months =
IF (
MAX ( Table1[Index] ) > 12,
AVERAGEX (
DATESINPERIOD (
Table1[Date],
LASTDATE ( 'Table1'[Date] ),
-12,
MONTH
),
CALCULATE (
SUM ( Table1[Values] )
)
),
DIVIDE (
CALCULATE (
SUM ( Table1[Values] ),
FILTER (
ALL ( Table1 ),
Table1[Index] <= 12
)
),
12
)
)
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
@twister8889
Use this measure :
New Avg =
VAR IND = SELECTEDVALUE('Table'[Index])
VAR _12 =
CALCULATE(
AVERAGE('Table'[Value]),
'Table'[Index] <= 12,
ALLSELECTED('Table')
)
VAR _OVER12 =
CALCULATE(
AVERAGE('Table'[Value]),
'Table'[Index] <= IND && 'Table'[Index] > IND - 12
,ALLSELECTED('Table')
)
RETURN
IF( IND > 12,
_OVER12,
_12
)
________________________
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
I will test, but I think that it is the correct solution, thank you so much.
Let me try to test this solution until Monday to check as a solution ok? More one time, thank you so much.
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |