Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM. Register now.

Reply
tdhlonghorn
Helper I
Helper I

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

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):

  TYLY      
 Start Date2/1/20222/2/2021      
 End Date1/31/20232/1/2022      
          
TY IncludeLY IncludeDateProduct RevenueInitial Margin $Landed Margin $Gross Margin $Initial CostIncremental Landed CostIncremental Fully Loaded Cost
NoNo2/1/2021 $  52,142 $  35,978 $  31,661 $  23,386 $  16,164 $     4,317 $     8,275
NoYes2/2/2021 $  46,985 $  33,359 $  29,023 $  21,016 $  13,626 $     4,337 $     8,006
NoYes2/3/2021 $  80,653 $  57,264 $  50,392 $  39,512 $  23,389 $     6,872 $  10,880
NoYes2/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):

 TYLYTY %LY %
Gross Margin $   9,444,734 $   8,739,49650.9%45.7%
Initial Cost $   5,090,257 $   5,735,63727.4%30.0%
Incremental Landed Cost $   2,040,969 $   1,614,08711.0%8.4%
Incremental Fully Loaded Cost $   1,993,702 $   3,016,07010.7%15.8%

 

Desired Output:

tdhlonghorn_0-1692115977132.png

 

TIA,

Tom

4 REPLIES 4
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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?

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.