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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
harirao
Post Prodigy
Post Prodigy

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,
Bar_Gp.PNG
But unable to create a single Graph
Bar_Gp2.PNG

Any suggestion?

 

Thanks,
Hari

1 ACCEPTED SOLUTION

@harirao 

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

Musadev_0-1709095241616.png

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

 

View solution in original post

19 REPLIES 19
harirao
Post Prodigy
Post Prodigy

Hi @Musadev 

I have just uploaded sample pbix file, pls find the below link to access 
https://drive.google.com/file/d/1lDc2W7vN0chh7n3xevJHDcqQJ2FwLJyM/view?usp=sharing


Regards,
Hari

@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

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

Bar_Gp5.PNG

Thank you


Regards,
Hari

@harirao 

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

Musadev_0-1709095241616.png

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

 

Hi @Musadev 

Thank you for your assistance. After excluding blanks, it's working well.

I have another query regarding formatting. Is it possible to display both month names as "Nov-22" and "Nov-23" when tooltips are enabled? Additionally, could each month's names be shown next to each other like "Nov-22 || Nov-23"? This formatting would be helpful for presentations.

Bar_Gp6.PNG

Thank you once again for your help and assistance.

Regards,
Hari

Hi @Musadev 

It's possible to create a visualization as described above. If it is, could you please suggest the logic to develop it?

Thanks,

 

Thanks,
Hari

 

Hi @harirao 
You can add the month-year into the tooltip however on the same visual i need to check it first and then i will update you here. 

Hi @Musadev 

Thank you I will be waiting for your response, In the meantime, I attempted to add the Month-Year into the tooltip, but it seems to be displaying only previous months. 

Regards,
Hari Krishna 

Hi @harirao 
Please mark as Solution all the replies relevant to your multiple answers. This will really help all new members to get solutions to one question.

Add a tooltip page and add 2 cards to it. 
One for the current month-year and the other for the Last month-year
Link the visual with your Tooltip page and the results will be like shown in the image. 
DAX Script for the Last year.

 

Mon LY = SAMEPERIODLASTYEAR(TBL1[Month])

 

Musadev_0-1709225639111.png

 

Good Luck with your learning journey. 

Hi @Musadev,

Can you please share me the sample PBIX file, as i tried above logic, not getting correct result as mentioned.

Thank you

 

Regards,
Hari

Hi @harirao 

Please check the PBIX File 

Chech the visual filter and remove the null values for last year. I will update the pbix file and will it tomorrow 

@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. 

harirao
Post Prodigy
Post Prodigy

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

Bar_Gp4.PNG

Thank you

 

Regards,
Hari 

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. 

harirao
Post Prodigy
Post Prodigy

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 

Bar_Gp3.PNG

Thanks,
Hari

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. 

Musadev_0-1709033018661.png

 

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. 

Musadev_1-1709033150697.png

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. 

Musadev_2-1709033303067.png

 

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

 

 

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
Resolver III
Resolver III

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. 

 

Musadev_0-1709019628314.png

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.