Display changes over years of different vendors

[Fox5]

Hi everyone

Is there a simple way to show changes in sales per customer for each year?

I have the following tables:

Customer 

No

Name

Invoice

InvoiceNo

CustomerNo

PostingDate

Amount

Now, I want do create a bar chart showing the difference per year for each customer. How can this be achieved?

Any help is much appreciated.

[Adamboer]

You can achieve this by using Power BI or any other data visualization tool. Here are the steps you can follow:

1. Import the Customer and Invoice tables into Power BI.

2. Create a relationship between the CustomerNo column in the Invoice table and the No column in the Customer table.

3. Create a measure that calculates the total sales amount for each customer and year. For example:

Total Sales = SUM(Invoice[Amount])

1. Create a table that shows the total sales amount for each customer and year. Add the Customer Name column from the Customer table, the PostingDate column from the Invoice table (grouped by year), and the Total Sales measure.

2. Create a bar chart using the table from step 4. Set the X-axis to the PostingDate column and the Y-axis to the Total Sales measure. Add a legend for the Customer Name column.

3. Add a secondary measure that calculates the difference in sales between years. For example:

Sales Difference = VAR CurrentYear = MAX(Invoice[PostingDate]) VAR PreviousYear = CALCULATE(MAX(Invoice[PostingDate]), DATEADD(Invoice[PostingDate], -1, YEAR)) RETURN CALCULATE([Total Sales], Invoice[PostingDate] = CurrentYear) - CALCULATE([Total Sales], Invoice[PostingDate] = PreviousYear)

1. Add a second bar chart to show the sales difference for each customer and year. Set the X-axis to the PostingDate column, the Y-axis to the Sales Difference measure, and the legend to the Customer Name column.

You should now have two bar charts that show the total sales and sales difference for each customer and year.

[Fox5]

Thanks for your help. As soon as I insert the measure "Sales Difference" to the chart's Y axis, the visual can't be displayed anymore.

[Fox5]

A quick update:

"Customer No", "Posting Date", "Amont" are now alll in the same table. The question remains how to compare the annual changes in a way that enables the use of a bar chart.

[amitchandak]

@Fox5 , You can create a date table joined with your  date of table and can use time intellignece

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

more

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Date table code

Date =

=
var _tab = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Qtr Start Date", var _rem = mod(month([Date]),3)
return eomonth([Date], Switch(_rem,2,-1, 0,-2, 1,-3))+1

)
return
Addcolumns(_tab,
"Qtr Rank", rankx(_tab,[Qtr Start Date], ,asc,dense),
"Qtr Day", datediff([Date],[Qtr Start Date], day)+1
)

[Fox5]

Thank you for your help 😊

Does your suggestion respect the per customer for each year problem? I thought about creating a calculated table that would look somewhat like this and the data of the bold columns would be used to create the bar chart:

CustomerNo	Amount 2018	Amount 2019	Diff	Amount 2020	Diff	Amount 2021	Diff
A	10	12	2	10	-2	29	19
B	12	12	0	18	6	20	2
C	20	18	-2	36	18	26	-10
D	5	30	25	29	-1	1	-28