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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

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
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

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.