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.

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

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