Forum Discussion

Anonymous

Not applicable

2 years ago

4Q Rolling Average (Cumulative Average) Include 0 value

Dear Team - I am trying to calculate each four Quarter Rolling Average which include 0 value as my formulas does not works well. Its excluding zero value and calculate measure. But i need to inc...

Super User

2 years ago

Hi Anonymous ,

Your measure works fine on my end. Are those zeroes actually zeroes or they're blank because your fact table doesn't have data in those quarters/those quarters do not exist in your fact table?

Anonymous
Not applicable
2 years ago
My fact table doesn't have data in those quarters so i assign as AOP = Blank then 0 else value
IF([AOP$]=BLANK(),0,[AOP$]) I think average 4 Quarter doesn't work where we have a zero value/Blank

formula perfectly will work all quarters have value incase blank / zero value doesn't work please suggest how to overcome this issue
- Anonymous
  Not applicable
  2 years ago
  Hi Anonymous ,
  
  Please do this in the power query, as the measure is not an actual column of data and is only calculated when used.
  
  Hope it helps!
  
  Best regards,
  Community Support Team_ Scott Chang
  
  If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
  - Anonymous
    Not applicable
    2 years ago
    Hi Scott - I tried power Query first to fix this as different came out then we developed in Measure to get a solution.
Anonymous
Not applicable
2 years ago
absolutlely you're right.. My fact table doesn't have value on specific date 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