Forum Discussion
Help spreading data across X axis in Stacked Column Chart
Hello,
I've been driving myself crazy for days trying to get to a solution here so I'm reaching out to the public for help! I have a table with sales data by date, and my desired output is a stacked column chart with the following elements:
- Gross Margin
- Initial Cost
- Incremental Landed Cost
- Incremental Fully Loaded Cost
Essentially trying to visualize the makeup of our costs & margins. This is obviously fairly straightforward, but where it gets tricky is trying to analyze against prior years because our calendar is a bit out of the ordinary:
- 2/1 fiscal start date
- we look day of week:day of week, so the built in time intelligence isn't helpful
- this is particularly tricky because when looking at a full year's worth of data, it looks like this:
- 2022: 2/1/22 - 1/31/23
- 2021 same period: 2/2/21 - 2/1/22
- this is particularly tricky because when looking at a full year's worth of data, it looks like this:
As a result, I have report slicers set up to do the work for me, but those slicers aren't helpful when I want to add fiscal year to the X axis. I've approximated how this would work in Excel by calculating an interim table and using that for my chart, but I can't figure out how to structure said interim table in DAX, and, more importantly, how to ultimately get to the visual.
Pasting my Excel data below; I'd be happy to include a file or pull this into a .pbix but I don't see the option to attach here.
Raw Data Sample (Include columns are a simple conditional using the start and end dates at the top):
| TY | LY | ||||||||
| Start Date | 2/1/2022 | 2/2/2021 | |||||||
| End Date | 1/31/2023 | 2/1/2022 | |||||||
| TY Include | LY Include | Date | Product Revenue | Initial Margin $ | Landed Margin $ | Gross Margin $ | Initial Cost | Incremental Landed Cost | Incremental Fully Loaded Cost |
| No | No | 2/1/2021 | $ 52,142 | $ 35,978 | $ 31,661 | $ 23,386 | $ 16,164 | $ 4,317 | $ 8,275 |
| No | Yes | 2/2/2021 | $ 46,985 | $ 33,359 | $ 29,023 | $ 21,016 | $ 13,626 | $ 4,337 | $ 8,006 |
| No | Yes | 2/3/2021 | $ 80,653 | $ 57,264 | $ 50,392 | $ 39,512 | $ 23,389 | $ 6,872 | $ 10,880 |
| No | Yes | 2/4/2021 | $ 95,058 | $ 67,491 | $ 56,018 | $ 42,519 | $ 27,567 | $ 11,474 | $ 13,498 |
Summary Table (=SUMIF calculations of the last 4 columns):
| TY | LY | TY % | LY % | |
| Gross Margin | $ 9,444,734 | $ 8,739,496 | 50.9% | 45.7% |
| Initial Cost | $ 5,090,257 | $ 5,735,637 | 27.4% | 30.0% |
| Incremental Landed Cost | $ 2,040,969 | $ 1,614,087 | 11.0% | 8.4% |
| Incremental Fully Loaded Cost | $ 1,993,702 | $ 3,016,070 | 10.7% | 15.8% |
Desired Output:
TIA,
Tom
4 Replies
Hi tdhlonghorn ,
Have you setup the fiscal dates columns (year, month and so on) on your calendar table? That would assist to get the correct calculation.
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.Thank you for the reply and guidance MFelix. I do have fiscal year & month in my calendar, but I don't know how to treat instances like 2/1 of ever year that can potentially fall in the current or prior year when considering a full year's data.
I uploaded my existing calendar with the sample data in the linked .pbix - please let me know if there's anything I can add/modify to make things easier.
Hi tdhlonghorn,
The file is requesting a password for access.
Concerning the question you have, your fiscal year always starts at 2/1 ? Or do you have any variance on that? Based on you last remark it appears that the fiscal year start may be shifting is that correct?
