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 a...
  • 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.

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!