Stacked Bar Chart

[redwins]

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 · Accepted solution]

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]

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.

[v-ljerr-msft · Accepted solution]

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.

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

[redwins]

Hello v-ljerr-msft, thanks for the tip.  I'll test it out.

[Phil_Seamark · Accepted solution]

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]

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

- Rob