The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
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
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...
User | Count |
---|---|
25 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
28 | |
13 | |
12 | |
12 | |
6 |