Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to Solution.
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.
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
Thank you
Regards,
Hari
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
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])
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
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.
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
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.
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
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
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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
111 | |
59 | |
57 |