Forum Discussion

pe2950's avatar
pe2950
Icon for Helper I rankHelper I
9 years ago
Solved

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 ...
Need Help
  • Sean's avatar
    Sean
    9 years ago

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

    Hope this helps! :smileyhappy:

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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.

 

Ena's avatar
Ena
Frequent Visitor
3 years ago

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?