Forum Discussion

Anonymous's avatar
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 include to find each four Quarter Rolling Average.

Measure and example attached here.

 

Please guide me.

 

4Q_AOP$ =
VAR __LAST_DATE = ENDOFQUARTER('Calendar'[Date])
VAR __PERIOD = DATESBETWEEN(
            'Calendar'[Date],
            STARTOFQUARTER(DATEADD(__LAST_DATE, -3, QUARTER)),
            ENDOFQUARTER(DATEADD(__LAST_DATE, 0, QUARTER))
        )
RETURN
CALCULATE(AVERAGEX (
    VALUES('Calendar'[Year-Qtr]),
    [AOP$]
),__PERIOD)
 
Dashboards

14 Replies

  • danextian's avatar
    danextian
    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's avatar
      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's avatar
        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's avatar
      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

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Does this measure work?

    Measure = averagex(summarize(calculatetable(Calendar,datesbetween(calendar[date],edate(min(calendar[date]),-3),max(calendar[date]))),Calendar[Year-Qtr],"A",[AOP$]),[A])

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Ashish - I tried the formula as doesn't works and result as follows..

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        Hi,

        Share the download link of the PBI file.  In that show the problem very clearly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Team,

     

    We need to open PBIX File then hope we understand better as Average for 4 Quarter not working few yellow highlighed periods in the measure.

     

    I've uploaded PBIX File into Google Drive and don't know how to share the file other way.

     

    Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

     

    I try to get each rolling 4 Quarter Average incase particular quarter no data found in Table then it should consider as Zero for the specific quarter and calculate average and tried the multiple way of formuls as doesn't works. shared the file in Google drive in sample file and i don't know how to share other way around. Thanks!