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 inc...
Dashboards
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

        Hello,

         

        Question - 1 = Table 2 DAX Formula

         

        Date : 30-09-2022 Average 4Q Wrong (highlighed)

        Date : 30-09-2023 Average 4Q Wrong (highlighed)

         

        Question - 2 = Table 1 & Table 2

         

        Example : I used 3 years data then 12 Quarter should be fixed incase no data then zero value in 'date' and all other columns

         

        Both tables 12 rows fixed.. You can check while changing filter

        PBIX File:

        https://drive.google.com/file/d/1rcyzr51WAq74yNUbIV81CjPqhBlYYyAN/view?usp=drivesdk