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.
Good day,
I have a dataset as per below structure covering 10+ years and 1000's of rows,
Name | Date | Yr 1 | Yr 2 |
Opp A | 02-01-2020 | 9 | 25 |
Opp B | 10-01-2019 | 3 | 11 |
and am looking to plot a stacked column chart using 'Yr 1' values for the year of 'Date' and 'Yr 2' for the following year.
| 9 |
|
3 | 11 | 25 |
2019 | 2020 | 2021 |
Even better would be to plot by month ie. for 'Opp A' plot 9 / (remaining months in year = 11) for 2020 while 25 / 12 months for 2021. This would allow the users to drill down on the date field.
Solved! Go to Solution.
You might think about un-pivoting your incoming data
Name | Date |
|
| Calculated Year |
Opp A | 02-01-2020 | Yr 1 | 9 | 02-01-2020 |
Opp A | 02-012020 | Yr 2 | 25- | 02-01-2021 |
Then a measure could just say, if Yr 1 use the date as is, if Yr 2 add one year to the date
Help when you know. Ask when you don't!
You might think about un-pivoting your incoming data
Name | Date |
|
| Calculated Year |
Opp A | 02-01-2020 | Yr 1 | 9 | 02-01-2020 |
Opp A | 02-012020 | Yr 2 | 25- | 02-01-2021 |
Then a measure could just say, if Yr 1 use the date as is, if Yr 2 add one year to the date
Help when you know. Ask when you don't!
Thank you for your reply.
Un-pivoting works a treat and combined with a calcualted column for the date the correct chart can be produced. 👍
Any ideas about how to go down to month level, ie. show the average by month instead of the yearly totals (9 and 25) ?
The final outcome should allow the user to drill down from year to quarter and month on the chart.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |