Forum Discussion

harirao's avatar
harirao
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

Single BAR Graph for Comparing Previous Year each months V/s Current Year Each Months

Hi Team 
I want to create a bar chart were i have do comparison Previous Year Months Vs Current Year months in same Bar graph, able to create two separate graphs as below,

But unable to create a single Graph


Any suggestion?

 

Thanks,
Hari

data protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Musadev's avatar
    Musadev
    2 years ago

    harirao 

    Check the results in the screenshot below and the PBIX file in the shared link.
    Link:  File.pbix 

    If you get your complete solution, please mark it as solution, so it will help others to find the solution. 

     

19 Replies

  • Musadev's avatar
    Musadev
    Icon for Resolver III rankResolver III
    2 years ago

    Hi harirao 
    You can add both scenarios in a single visual by adding both measures to a stacked column chart.
    Add the Date variable in the X-Axiz and 2 measures in the Y-Axis and you will get the results as shown below. 

     

    Dax Measure for the Last year's sales and this year's Sales are here. map and link it to your data. Thanks

     

    Sales Amount = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

     

    Sales Amt Last Year = 
CALCULATE( Sales[Sales Amount],
SAMEPERIODLASTYEAR(Sales[Order Date])
)

      

    Good luck for the learning and if it helps mark it as a Solution for your Query. Thanks

  • harirao's avatar
    harirao
    Icon for Post Prodigy rankPost Prodigy
    2 years ago

    Hi Musadev 

    Actually i dont have Quantity & Date column, only have Month & Values, by using this Data i have to create Previous Year Months Vs Current Year month Calender Year {NOV to OCT} i.e (Nov-22/Nov-23..........until Oct-23/Oct-24) in SAME BAR graph 



    Thanks,
    Hari

    • Musadev's avatar
      Musadev
      Icon for Resolver III rankResolver III
      2 years ago

      wait let me add your data to my data set. and I will update it, you can use the same records, so no worries

      If you can share the sum of values DAX i will transform it for last year as well(If measure is used) else only sum of amount on the visual then i will use static values

    • Musadev's avatar
      Musadev
      Icon for Resolver III rankResolver III
      2 years ago

      Hi harirao 

      Please check the following steps. I have used dummy data, for the first year your data will be blank. and after one year you can compare the bars/values.
      I have added additional SID column to add up all the rows and get the value for the month. 

       

      I created a measure to sum the value. here is the DAX code

      Sum of Values = 
SUMX(TBL1,
TBL1[Value]
)

      After that, I checked the data in a visual and it is coming as you have shown. 

      Now i have added another Measure to get the amount of Value for the month of last year. 

      Sum of Values LY = 
CALCULATE( [Sum of Values],
SAMEPERIODLASTYEAR(TBL1[Month])
)

      i have added a stacked column Chart to the report pane. and also check the data in the table view below as well. 

       

      If you are still have issues then share the PBIX file with masked data and i will validate it. Thanks

       

       

  • harirao's avatar
    harirao
    Icon for Post Prodigy rankPost Prodigy
    2 years ago

    Hi Musadev 
    Can you please let me know are you looking for sample data?
    I just created bifurcation between Previous Year months and current year months



    Thank you

     

    Regards,
    Hari 

    • Musadev's avatar
      Musadev
      Icon for Resolver III rankResolver III
      2 years ago

      hI harirao 
      I am in the office and working. if you can send me the data (insert scripts to Oracle DB) or share pbix file with only the required data. i will check it out now. Otherwise i will need to check it after office hours. 

    • Musadev's avatar
      Musadev
      Icon for Resolver III rankResolver III
      2 years ago

      harirao  
      You are using date as text and it is causing issues. your Month in both tables should be lin date format (Mmm-yy) and then it will work. I am share the file after transformation. 

    • Musadev's avatar
      Musadev
      Icon for Resolver III rankResolver III
      2 years ago

      harirao 
      I have performed multiple transformations to your sheet. 
      1- Split the text date into Month (Mmm) and Year (yyyy) and then concatenate them into a new column Month Year.

      2- The above steps for both tables

      3- Change the relationships to the new columns Month Year

      4- Add 2 measures, one for this year total value and one for last year. (check Key Measures table)

      5- check the tables and the chart for more details.

       

      Here is PBIX file. 
      https://drive.google.com/file/d/1EPkxiucQzPiWlfsTiljK_9upLQRhbYUY/view?usp=sharing

      • harirao's avatar
        harirao
        Icon for Post Prodigy rankPost Prodigy
        2 years ago

        Hi Musadev 
        Thank you so much for providing Solution which is giving correct result.
        However after creating Clustered Column Chart noticed that Previous Year Month & Current Year Months are not next to each other.
        If i try to arrange Nov-22 is coming as single Bar and in middle Nov-23 is coming with two bar's that is Total Value LY & Total Value TY Stating/Showing Nov-23 only as i want to See Nov-22& Nov-23

        Thank you


        Regards,
        Hari