Forum Discussion

mgarcianxp's avatar
mgarcianxp
Frequent Visitor
3 years ago
Solved

Calculating quarterly historical average

I am new to Power BI, and dont have much DAX experience. I've tried to find a post that helps me with this, but have not found any. 

I am trying to replicate the excel chart below in PowerBI and I am having trouble to write the DAX code for the historical average (red line). 

The DOI values are not in a table but they are a measure. The historical average is supposed to be the 5-years quarterly seasonality: Per quarter: average of the DIO from 2015 to 2019. So basically, I need to calculate 4 values and repeate them on each quarters for all years. 

Thanks for the help!! 

 

 

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi mgarcianxp ,

     

    I suggest you to try to create a CALENDAR table to help calculation.

    Calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Quarter", QUARTER ( [Date] ),
    "Month", MONTH ( [Date] )
)

    Create a relationship between 'Calendar'[Date] and 'Fact Table'[Date].

    AVERAGE BY QUARTER =
AVERAGEX (
    ALLEXCEPT ( Calendar, Calendar[Year], Calendar[Quarter] ),
    'Fact Table'[DIO]
)

    If this reply still couldn't help you solve your issue, please share a sample file with me.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

    • mgarcianxp's avatar
      mgarcianxp
      Frequent Visitor
      3 years ago

      Hi some_bih , 

      thank you for the answer. 

      I am not looking for the rolling average, but the historical average is static. Anyway I have solved it in a probabl not very efficien way, by creating a calculated table instead of a measuers table, then filtering it 4 times for the 4 quarters to calculate the average of each quarter, so I can have a measure that repeats itselve thoruout the yerars. 

      Not very elegant, but it's working 🙂 

      thansk again 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi mgarcianxp ,

         

        I suggest you to try to create a CALENDAR table to help calculation.

        Calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Quarter", QUARTER ( [Date] ),
    "Month", MONTH ( [Date] )
)

        Create a relationship between 'Calendar'[Date] and 'Fact Table'[Date].

        AVERAGE BY QUARTER =
AVERAGEX (
    ALLEXCEPT ( Calendar, Calendar[Year], Calendar[Quarter] ),
    'Fact Table'[DIO]
)

        If this reply still couldn't help you solve your issue, please share a sample file with me.

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • mgarcianxp's avatar
    mgarcianxp
    Frequent Visitor
    3 years ago

    Hi again, 

    still struggling with the average of a measure. 

    I solved the issue above by creating a table instead of working with measures. This is working fine for the total values (aggregate of all the companies). But I still want to do it without the additional table. 

     

    This is how the average is calculated in the case where DOI is a column: 

    The Average is calculated as follows: 

    Q1 DOI Average = CALCULATE(AVERAGE('|Table OEM'[OEM DOI]),ALLEXCEPT('|Table OEM','|Table OEM'[Date].[Quarter]),AND('|Table OEM'[Qtr]="Q1",'|Table OEM'[Date] <= DATE(2019,12,31)))
     

    This is the calculation in the case where DOI is a measure: 

    DOI Q1 Avrg. = CALCULATE(AVERAGEX('|Measures OEM','|Measures OEM'[DOI per OEM]),ALLEXCEPT('|Calendar','|Calendar'[Date].[Quarter]),AND('|Calendar'[Qtr]="Q1",'|Calendar'[Date] <= DATE(2019,12,31)))

     

    The both tables below show the results for both cases. How can I get the results of the left table using the measure? Thanks!