Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi
I am trying to create line/bar chart for total amount where line chart is previous year and bar chart is current year but couldn't make it work(I was able to get clustered bar chart). Any ideas? I am connecting to Snowflake using DirectQuery.
Thanks!
Hi Uzi, I was able to get it work with sample data as per your suggestion but not with actual data, got an error -- a date column containing duplicate dates was specified in the call to function.
I included duplicate entry to test with sample data and it was fine. Not sure why its throwing an error on actual data set. Is this something to do with date table? Currently I am not using date table.
CREATED_YEAR_NUM | CREATED_MONTH_NUM | AMOUNT | DATE |
2023 | 1 | 110000 | 1/1/2023 |
2023 | 2 | 90000 | 2/1/2023 |
2023 | 3 | 40000 | 3/1/2023 |
2023 | 4 | 140000 | 4/1/2023 |
2023 | 5 | 50000 | 5/1/2023 |
2023 | 6 | 10000 | 6/1/2023 |
2023 | 7 | 100000 | 7/1/2023 |
2023 | 8 | 60000 | 8/1/2023 |
2023 | 9 | 120000 | 9/1/2023 |
2023 | 10 | 20000 | 10/1/2023 |
2023 | 11 | 130000 | 11/1/2023 |
2023 | 12 | 160000 | 12/1/2023 |
2024 | 1 | 150000 | 1/1/2024 |
2024 | 1 | 5000 | 1/1/2024 |
2024 | 2 | 30000 | 2/1/2024 |
2024 | 3 | 70000 | 3/1/2024 |
2024 | 4 | 80000 | 4/1/2024 |
2024 | 5 | 170000 | 5/1/2024 |
Hi @ritha-m
yes , It is because of date table. because Dateadd function works of continuation of date.
better if you can create calendar table with below dax
go to modeling tab > select new table> then create below dax
then connect your date column with Calendar table.
then take your calendar date in your both the measures
I hope I answered your question!
Hi Uzi,
Couple things going on here. Initially I created calendar table but disabled as I got multiple dates to work with. Also I realized all my dates are in dimensions table and when I map calendar table to dates in dimension table; the graphs still not working as I can't connect date table to facts table.
1. Should I ask my data team move those dates to facts table?
2. What's the best way when we have multiple dates
Structre:
Date Table
Dimension Table - Orders with Created Date and Close Date (other columns)
Facts Table - OrderID, Amount (other columns)
Thanks
Hi @ritha-m
You can create a seperate measure for Previous year line chart and seperte measure for current year bar chart
CY sales= Calculate(Sum(sales), DateAdd('Date',0,Year))
PY sales= Calculate(Sum(sales), DateAdd('Date',-1,Year))
Then put CY sales on Y axis and PY sales on Line axis.
I hope I answered your question!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |