Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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
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.
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
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.
Hello Phil, thanks, I'll test it out.
- Rob
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |