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

[harirao]

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

[harirao]

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

[Musadev]

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

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

[Musadev · Accepted solution]

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

[harirao]

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.

Thank you once again for your help and assistance.

Regards,
Hari

[harirao]

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

[Musadev]

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. 

[harirao]

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 

[Musadev]

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])

Good Luck with your learning journey. 

[harirao]

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

[Musadev]

Hi @harirao 

Please check the PBIX File 

[Musadev]

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

[Musadev]

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

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]

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]

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]

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

[Musadev]

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]

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