Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
89 | |
86 | |
75 | |
69 | |
68 |
User | Count |
---|---|
215 | |
128 | |
117 | |
82 | |
76 |