line/bar chart for previous year and current year

[ritha-m]

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!

[ritha-m]

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

[Uzi2019]

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

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!

[ritha-m]

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]

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!

[Arul]

@ritha-m ,

Could you share some sample data?