Comparison of previous years average with current months average in the bar graph

[mokhan]

Hello All,

I am a beginner with Power BI.

I have a column with date and another column with values. I would like to generate a bar graph beginning with previous years average bar follwed by current months average bars. Something like below.

How can I acheive this using Power BI. Any help would be appreciated!

Thankyou!

[v-yingjl · Accepted solution]

Hi @mokhan ,

Based on your description, you can create a calulated column like this to use it as X-axis:

Axis = 
IF (
    YEAR ( 'Table'[Date] ) = YEAR ( TODAY () ),
    'Table'[Date].[Month],
    CONVERT ( YEAR ( 'Table'[Date] ), STRING )
)

Then create these to measures, one of them calculates average about each year and each month for current year, another one calulates average about all years as a target:

Avg = AVERAGE('Table'[Values])
Avg_allyears = 
CALCULATE ( AVERAGE ( 'Table'[Values] ), ALL ( 'Table' ) )

Use a 'Line and stacked column chart ' to show it:

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ToddChitt]

@mokhan You asked this in the Power Query forum. This is probably better asked in the Desktop forum. But that won't stop people from trying to help you with your DAX

If I understand correctly, you would first need to get the MONTHLY SUM of your Metric, and from there, get the AVERAGE of those sums for the LAST YEAR. Is that correct?

Start with two calculated columns in your table: YEAR(Date) and MONTH(Date):

TheYear = YEAR([Date]) and TheMonth = MONTH([Date])

Then add a derived table in DAX:

Monthly Sums = SUMMARIZE ( <fact table name>, [TheYear], [TheMonth], "Sum of Metric", SUM( [Some column] ) )

Refer to this for SUMMARIZE: SUMMARIZE function (DAX) - DAX | Microsoft Docs

Finally, create a MEASURE as:

Average Monthly = AVG ( 'Montly Sums' [Sum of Metric] )

Hope this helps