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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ritha-m
New Member

line/bar chart for previous year and current year

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!

ritham_0-1715821628862.png

 

5 REPLIES 5
ritha-m
New Member

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_NUMCREATED_MONTH_NUMAMOUNTDATE
202311100001/1/2023
20232900002/1/2023
20233400003/1/2023
202341400004/1/2023
20235500005/1/2023
20236100006/1/2023
202371000007/1/2023
20238600008/1/2023
202391200009/1/2023
2023102000010/1/2023
20231113000011/1/2023
20231216000012/1/2023
202411500001/1/2024
2024150001/1/2024
20242300002/1/2024
20243700003/1/2024
20244800004/1/2024
202451700005/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

Uzi2019_0-1715847414368.png

 

Calendar = CALENDAR(MIN(Table[Start Date]),MAX(table[Start Date]))

 

then connect your date column with Calendar table. 
then take your calendar date in your  both the measures

 

I hope I answered your question!

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

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

  • userrelationship method or
  • unpivot the table with multiple dates?

Structre:

Date Table

Dimension Table - Orders with Created Date and Close Date (other columns)

Facts Table - OrderID, Amount (other columns)

 

Thanks

Uzi2019
Super User
Super User

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!

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
Arul
Super User
Super User

@ritha-m ,

Could you share some sample data?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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