Help spreading data across X axis in Stacked Column Chart

[tdhlonghorn]

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

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

[MFelix]

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.

[tdhlonghorn]

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.

Sample Data

[MFelix]

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?

[tdhlonghorn]

Unfortunately I have no idea how to remove the password protection. Our fiscal year always starts 2/1, but for comparison purposes we will always be comparing 1/31 (the last day of the fiscal year) to 2/1 because it is the same day of week.