Forum Discussion

redwins's avatar
redwins
New Member
9 years ago
Solved

Stacked Bar Chart

I have data that spans from January 2016 through January 2017.  The data encompasses creation date, creation month, and creation year.  The data items are prioritized 1 through 4.  For my existing charts I use:

- Axis: Created Month

- Legend: Priority

- Value: Count of Status

This works well as the data is within one year.

 

I would like to create a Bar Chart for 2017 that shows each month of 2016 next to their respective month for 2017. Any guidance would be apprecaited.

  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    You'll need to create a measure that will be called something like [Count of Status prev year]

     

    The pattern should be something like :

     

    Count of Status Prev Year = CALCULATE(
				COUNTROWS('<yourTable>'),
				PARALLELPERIOD('Dates'[Date],-12,MONTH)
				)

    This assumes you have a related date table called 'Dates' with a column called date.

  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi redwins,

     

    According to your description above, you should be able to use the formula below to create the measure for "Count of Status Prev Year", then show it with "Count of Status" in the Value field of a Clustered column chart, and apply a visual level filter to limit the date to only 2017 for the chart.:smileyhappy:

    Count of Status Prev Year =
VAR currentYear =
    YEAR ( MAX ( Table1[creation date] ) )
VAR currentMonth =
    MONTH ( MAX ( Table1[creation date] ) )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALL ( Table1 ),
            Table1[creation year]
                = currentYear - 1
                && Table1[creation month] = currentMonth
        )
    )

    Note: replace "Table1" with your table name in the formula above.

     

    Regards

5 Replies

  • redwins's avatar
    redwins
    New Member
    9 years ago

    I had some trouble with the Visual Level Filters and creating a measure to cluster the stacked chart.  I'll need to learn more about using these items. 

    I was able however to get this done by adding Created Year to the Axis Visualization and then use the Drill Down option within the chsrt area which seperated January 2016 and January 2017 into two different stacks.

    - Axis: Created Month
    - Axis: Created Year
    - Legend: Priority
    - Value: Count of Status

    The next thing I'll work on is coloring the years differently.  Thanks for your input.

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    You'll need to create a measure that will be called something like [Count of Status prev year]

     

    The pattern should be something like :

     

    Count of Status Prev Year = CALCULATE(
				COUNTROWS('<yourTable>'),
				PARALLELPERIOD('Dates'[Date],-12,MONTH)
				)

    This assumes you have a related date table called 'Dates' with a column called date.

    • redwins's avatar
      redwins
      New Member
      9 years ago

      Hello Phil,  thanks, I'll test it out.

      - Rob

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi redwins,

     

    According to your description above, you should be able to use the formula below to create the measure for "Count of Status Prev Year", then show it with "Count of Status" in the Value field of a Clustered column chart, and apply a visual level filter to limit the date to only 2017 for the chart.:smileyhappy:

    Count of Status Prev Year =
VAR currentYear =
    YEAR ( MAX ( Table1[creation date] ) )
VAR currentMonth =
    MONTH ( MAX ( Table1[creation date] ) )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALL ( Table1 ),
            Table1[creation year]
                = currentYear - 1
                && Table1[creation month] = currentMonth
        )
    )

    Note: replace "Table1" with your table name in the formula above.

     

    Regards