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,

 

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

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

    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's avatar
      Sean
      Icon for Community Champion rankCommunity Champion
      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:

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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 

    • 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?