Forum Discussion

puru85's avatar
puru85
Icon for Helper II rankHelper II
2 years ago
Solved

Combining Two Stacked Bar Charts into One

Hi Experts,

I have two stacked Bar Charts.

  1.  Selected MonthYear Sales. 
  2. Last Year Same Period Sales. 

 

I would like to combine these two Stacked bar Charts.

 

Here are the details of the existing charts:

  1. Selected MonthYear Sales

X-Axis: MonthYear
Y-Axis: Sales (calculated as SUM(Orders[Sales]))
Legend: Day

 



   2. Last Year Same Period Sales

X-Axis: LastYearMonth
Y-Axis: LastYearSales (calculated with the following DAX formula):
CALCULATE(
SUM(Orders[Sales]),
FILTER(
ALL('Master Calendar'),
'Master Calendar'[Date] = MAX('Master Calendar'[LastYearDate])
))
Legend: Day

 


My goal is to merge these into a single chart, where each bar represents the sales data: one for the "Selected MonthYear" and another for the "LastYearMonth".

Attached is the PBIX:

SelectedMonthYear&LastYearSamePeriod.pbix

Anyone, Please help.

Thank you!


Need Help
Show and Tell
  • MFelix's avatar
    MFelix
    2 years ago

    Hi puru85 ,

     

    I think that you need to redo your Last Year sales I have redone it to:

    LastYearSales_ New Version= 
CALCULATE(
   SUM(Orders[Sales]),
    DATEADD('Master Calender'[Date], -1 , YEAR))

    Created a field parameter using the Sales and Last Year Sales New version and added the following measure:

    Measure Selection = SWITCH(SELECTEDVALUE(Parameter[Parameter Order]), 0, [Sales],1,[LastYearSales_ New Version])

    Create the visualization in the following way:

    • X-axis:
      • MonthYear
      • Parameter
    • Y-Axis
      • Measure Selection
    • Legend
      • Day

     

    See file attach.

     

     

  • MFelix's avatar
    MFelix
    2 years ago

    Hi puru85 ,

     

    After your question just got an easier solution:

    •  Create a table with the MonthYear column (disconnected table)
    • Add the following measure:
    Measure Selection = 
            SWITCH(
                TRUE(),
                SELECTEDVALUE('Month Table'[MonthYear]) in VALUES('Master Calender'[MonthYear]), [Sales],
                SELECTEDVALUE('Month Table'[MonthYear]) in  VALUES('Master Calender'[LastYearMonth]),[LastYearSales])
    • Create your visualization using the following setup:
      • X-Axis
        • MonthYear (from the new table)
      • Y-Axis
        • Measure Selection
      • Legend
        • Day

    If you select more than one month this will present the information for current year and previous years selection.

    See attach file.

4 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    2 years ago

    Hi puru85 ,

     

    I think that you need to redo your Last Year sales I have redone it to:

    LastYearSales_ New Version= 
CALCULATE(
   SUM(Orders[Sales]),
    DATEADD('Master Calender'[Date], -1 , YEAR))

    Created a field parameter using the Sales and Last Year Sales New version and added the following measure:

    Measure Selection = SWITCH(SELECTEDVALUE(Parameter[Parameter Order]), 0, [Sales],1,[LastYearSales_ New Version])

    Create the visualization in the following way:

    • X-axis:
      • MonthYear
      • Parameter
    • Y-Axis
      • Measure Selection
    • Legend
      • Day

     

    See file attach.

     

     

  • puru85's avatar
    puru85
    Icon for Helper II rankHelper II
    2 years ago

    Hi MFelix ,

    Thank you very much for your solution; it is much appreciated and will work for me.

    Out of curiosity, I have a quick question: Instead of having "Sales" and "LastMonthSales" as static X-axis labels, can we have a dynamic X-axis for Month and Year as shown below?



     

    Thank you



    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      2 years ago

      Hi puru85 ,

       

      After your question just got an easier solution:

      •  Create a table with the MonthYear column (disconnected table)
      • Add the following measure:
      Measure Selection = 
            SWITCH(
                TRUE(),
                SELECTEDVALUE('Month Table'[MonthYear]) in VALUES('Master Calender'[MonthYear]), [Sales],
                SELECTEDVALUE('Month Table'[MonthYear]) in  VALUES('Master Calender'[LastYearMonth]),[LastYearSales])
      • Create your visualization using the following setup:
        • X-Axis
          • MonthYear (from the new table)
        • Y-Axis
          • Measure Selection
        • Legend
          • Day

      If you select more than one month this will present the information for current year and previous years selection.

      See attach file.