I've been provided with the attached dataset and I'm wondering what the best way is to structure it. I'm planning to pull this into Power BI and want to do a bar graph that has Actual and Budget data bars with $'s as the bar value and months on the X-axis. I tried 2 approaches.
1) The first approach was to unpivot all of the columns at once. I created a new column to distinguish between actual, budget, and forecast and had all of the numbers in the same column. This is a clean way to structure the data but the problem I have is that because all of the numbers are in the same column so I couldn't graph Actual & Budget $'s side by side because they are part of the same field.
2) The second approach was to unpivot the actuals, forecast & budget columns separately (so there would be separate queries for each of these). This gives me dates and $'s for each category (ie. Actuals, Forecast & Budget) This allows me to place each category in a separate set of bars and I can place a date at the bottom, which is almost what I want. The problem is that I now have 3 different dates columns and I require one common date column instead of 3 different ones. I thought about using a calendar table and connecting the data tables by date, but I'm wondering if there's a simpler way to approach this. I feel like there is a simple solution to this, but I'm just not sure how to tackle it
Thanks
Lindsay
