Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear Team,
I would like to calculate moving average as my DAX Formuls didn't works well.
My fact table doesn't have value on specific date/month then my 4 Quarter average doesn't calculate as expected.
Example measurement takes where we don't have value in Fact Table then it takes SUM of Total / 4 as average (wrong answer) = 34,637
How can we fix this. pls suggest
Hi @RajK2 ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
Create two columns
YearQtr = CONCATENATE(LEFT([Year-Qtr],2),RIGHT([Year-Qtr],1))
Rank = RANKX('Table',[YearQtr],,ASC)
Create measure
Average 4 quarter =
VAR _sum =
CALCULATE(
SUM('Table'[AOP$]),
FILTER(
ALL('Table'),
'Table'[Rank] >= SELECTEDVALUE('Table'[Rank]) - 3 && 'Table'[Rank] <= SELECTEDVALUE('Table'[Rank])
)
)
RETURN
IF(
_sum = BLANK(),
0,
_sum/4
)
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...