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 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 Anonymous , 

    thanks for the reply, I managed to solve the issue in a different way 🙂 #

     

  • mgarcianxp's avatar
    mgarcianxp
    Frequent Visitor
    3 years ago

    I reworked my solution and used your proposal, as mine was less elegant 😉 thanks!