Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pe2950
Helper I
Helper I

Clustered Column Chart - Grouping by same month over years?

I'm trying to create a clustered column chart that would show a count of leads, by month, with each cluster representing the same month. 

 

I've got the data in the format i want it, the query has two columns one that is start of month date, and next is a sum column that is a count of the amount of leads. 

 

I can't quite get how to group the clusters to show the same months for previous years, such that cluster 1 would contain january 2017, january 2016, january, 2015etc.

 

I was trying to create a measure that would calculate the same month for the previous year, but can't quite get there. I can calculate previous year, and previous month, but not previous year, same month? 

 

Any advice?

 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@pe2950

Alternatively you can drag your Date field to the Axis and keep only the MONTH from the resulting Hierarchy

then drag your Date field again but this time to the Legend and keep only the YEAR

and finally place your Measure (which won't need any adjustments now because of the Legend) in the Value

If you have many years - you can limit what shows on the chart with a Slicer or some type of filter limiting the years

One thing to note about this approach is that it will let you compare months of different years not only consecutive years

for example Monthly data in 2016 vs monthly data in 2012 only

Like this...

Chart Demo - Compare Months.gif

Hope this helps! Smiley Happy

View solution in original post

7 REPLIES 7
v-chuncz-msft
Community Support
Community Support

@pe2950,

 

You could also try to use Pivot Column in Query Editor.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Phil_Seamark
Microsoft Employee
Microsoft Employee

Hi @pe2950,

 

Do you have some sample data that you can share and perhaps a mock up drawing of what you'd like the chart to look like?

 

Cheers,

 

Phil


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Data looks like this:

 

StartOfMonth     LeadCount     Year     Month

1/1/2017             100                2017      1

2/1/2017              90                 2017       2

etc...

 

Id like a column chart that looks like this: 

 

Column 1 = January 2016, Column 2 = January 107, Column 3 = Febuary 2016, Column 4 = Febuary 2017

 

I think i need a DAX with same period last year... 

Sean
Community Champion
Community Champion

@pe2950

Alternatively you can drag your Date field to the Axis and keep only the MONTH from the resulting Hierarchy

then drag your Date field again but this time to the Legend and keep only the YEAR

and finally place your Measure (which won't need any adjustments now because of the Legend) in the Value

If you have many years - you can limit what shows on the chart with a Slicer or some type of filter limiting the years

One thing to note about this approach is that it will let you compare months of different years not only consecutive years

for example Monthly data in 2016 vs monthly data in 2012 only

Like this...

Chart Demo - Compare Months.gif

Hope this helps! Smiley Happy

Anonymous
Not applicable

@Sean,

 

What if you have two seperate queries. For example, I have Membership enrollment from last fiscal year in one query and enrollment data from this year in another. I have them separate because I regularly update this year's data as the year progresses. So how can I have a clustered bar chart that has the number of enrollments last year next vs. the current number of enrollments with the queries being separate?

 

Sincerley,

Trent 

Hi @pe2950

 

I created some dummy data by creating the following calculated table...

 

Table = SELECTCOLUMNS(
                    CALENDAR(DATE(2016,1,1),TODAY()),
                    "Start Of Month" , DATE(
                                            YEAR([Date])
                                            ,MONTH([Date]),
                                            1),
                    "Lead Count" , INT(RAND() * 100) )

This just creates a two column table with a bunch of random numbers for the Lead Count.

 

I then added the following measure

 

Lead Count Last Year = CALCULATE(
                        SUM('Table'[Lead Count]),
                        SAMEPERIODLASTYEAR('Table'[Start Of Month])
                        )

I could then create the following chart.

 

sply.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

This is assuming you have just two years worth of data. What if you have more than two? Same Period Last Year will no longer work... What might then?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors