Forum Discussion

PowerNoob's avatar
PowerNoob
Frequent Visitor
3 years ago
Solved

Help with stacked columns with different x values in same data entry

I have a datset where the entries are by model / serial number. Each entry can have phase 1 and phase 2 builds with associated hours. I'm looking to create a stacked column display for model/serial build time by work week separating colors by the phase of the build. The first image below shows the scheduled time graph I'm building.

 

The problem I'm running into is that the graph is displaying everything summarized by the phase 1 schedule week. In other words Phase 2 hours are stacking on Phase 1 scheduled work week. How can I get phase 2 hours to show on the correct week? The second graph shows a selection where week 29 is selected, but includes also phase 2 hours for the build where phase 1 is schedule in week 29 (phase 2 is selected). How can i get the 135 hours to show in week 35?

 

I've tried a number of different sorts, and even separating the build hours into a separate table. I can't seem to separate phase 2 hours. 

 

Thanks in advance!

 

Need Help
  • PowerNoob's avatar
    PowerNoob
    3 years ago

    I solved this by:

    1. Creating a calender table based on week number with this DAX: 
      2. Week Num Base = VAR DateStart = MINX(tblSchedule,[P1Date].[Date]) VAR DateEnd = MAXX(tblSchedule,[ShipDate].[Date]) VAR simpledatetablesonlysunday = FILTER ( ADDCOLUMNS ( CALENDAR ( DateStart, DateEnd ), "weeknum", WEEKNUM ( [Date], 1 ) ), WEEKDAY ( [Date] ) = 1 ) RETURN simpledatetablesonlysunday
      2. Setting up a query that pulls P1 data and relating with a date key for year/work week in its own table
      3. setting up a separate query that pulls P2 data and relating with a date key for year/work week in its own table
      4. relating P1, P2, and work week tables by the date key
      5. graphing on a Line and Stacked Column Chart with calendar week number on X-axis, and P1 and P2 data on Y axis. 

     

1 Reply

  • PowerNoob's avatar
    PowerNoob
    Frequent Visitor
    3 years ago

    I solved this by:

    1. Creating a calender table based on week number with this DAX: 
      2. Week Num Base = VAR DateStart = MINX(tblSchedule,[P1Date].[Date]) VAR DateEnd = MAXX(tblSchedule,[ShipDate].[Date]) VAR simpledatetablesonlysunday = FILTER ( ADDCOLUMNS ( CALENDAR ( DateStart, DateEnd ), "weeknum", WEEKNUM ( [Date], 1 ) ), WEEKDAY ( [Date] ) = 1 ) RETURN simpledatetablesonlysunday
      2. Setting up a query that pulls P1 data and relating with a date key for year/work week in its own table
      3. setting up a separate query that pulls P2 data and relating with a date key for year/work week in its own table
      4. relating P1, P2, and work week tables by the date key
      5. graphing on a Line and Stacked Column Chart with calendar week number on X-axis, and P1 and P2 data on Y axis.