Highest ever Sales in Last 5 Years

[PBI_SUPERUSER]

I am new to power BI and sturggling with a simple problem which I couldn't get the required result.

I have a table with Daily Sales values with several other columns indicating product name,transactionId,customer etc.Using this I have created a matrix with last 5  years sales.I need to have a another column in my matrix with higest sales value in last 5 years.I have used below Dax to get the result but it returns the Last year sales amount not the maximum sales amount.Appreciate if anyone can help me on this.

Highest Ever Sales in Last 5 Years =

Var _matrix=SUMMARIZE(sales,calender[FN_YEAR],"Full Year Sales",sum(sales[QUANTITY]))

Var _max=MAXX(_matrix,[Full Year Sales])

return _max

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result very clearly.

[PBI_SUPERUSER]

Hi,

Required Out put is as below;

1. Sample data set is given below

Date	Qunatity	Customer	Product	Counrty	Financial Year
01/01/2011	19	A1	B1	ABC	FY10-11
03/01/2011	17	A2	B2	ABC	FY10-11
03/01/2012	4	A3	B3	ABC	FY11-12
03/01/2012	7,600	A4	B4	ABC	FY11-12
03/01/2012	51	A5	B5	DEF	FY11-12
03/01/2011	42	A6	B6	ABC	FY10-11
03/01/2011	72	A7	B7	ABC	FY10-11
01/01/2014	15	A1	B1	ABC	FY13-14
01/01/2014	18	A2	B2	ABC	FY13-14
01/01/2014	20	A3	B3	ABC	FY13-14
01/01/2014	144	A4	B4	ABC	FY13-14
01/01/2014	30	A5	B5	DEF	FY13-14
01/01/2014	189	A6	B6	ABC	FY13-14
01/01/2014	104	A7	B7	ABC	FY13-14
01/01/2015	11	A1	B1	ABC	FY14-15
01/01/2015	194	A2	B2	ABC	FY14-15
01/01/2015	114	A3	B3	ABC	FY14-15
01/01/2015	99	A4	B4	ABC	FY14-15
02/01/2015	23	A5	B5	DEF	FY14-15
02/01/2015	47	A6	B6	ABC	FY14-15
02/01/2015	23	A7	B7	ABC	FY14-15
01/01/2013	22	A7	B7	ABC	FY12-13
01/01/2013	102	A1	B1	ABC	FY12-13
01/01/2013	82	A2	B2	ABC	FY12-13
01/01/2013	10	A3	B3	ABC	FY12-13
02/01/2013	35	A4	B4	ABC	FY12-13
02/01/2013	30	A5	B5	DEF	FY12-13
02/01/2013	18	A6	B6	ABC	FY12-13

Thanks in advance!

[Ashish_Mathur · Accepted solution]

PBI file attached.

Hope this helps.

[PBI_SUPERUSER]

Thank you very much for the support.This is what I wanted.

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Does the Fy run from April - March?

[PBI_SUPERUSER]

Yes.Financial Year runs from Apr-Mar

[v-huijiey-msft]

Hi @PBI_SUPERUSER ,

Thanks for the reply from @AnalyticPulse , please allow me to provide another insight:

Please try:

Highest Ever Sales in Last 5 Years = 
CALCULATE(
MAX(sales[sales]), 
FILTER(
ALL(calender[FN_YEAR]), 
calender[FN_YEAR] > YEAR(TODAY()) - 5
)
)

Replace sales[sales]) with your own field.

This measure shows the columns with the highest sales in the last 5 years.

The page visualization is as follows:

The pbix file is attached.

If you have any other questions please feel free to contact me.

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[PBI_SUPERUSER]

Thanks for your time to provide me an answer.But my expected solution is different.Out of  last 5 years total sales values ,I need the value of higest value.

[AnalyticPulse]

you can use beow dax and let me know if that works:
Highest Ever Sales in Last 5 Years =
VAR MaxSales = CALCULATE( MAX( Sales[SalesAmount] ), LASTNONBLANK( Dates[Year], [Total Sales] ) )
RETURN
MaxSales

if this resolved your issue mark this reply as an aswer.

here are some blogs that can help you understand basic dax functions;
summarize and summarize column in powerbi

mastering-time-intelligence-with-power bi 
complex-real-life-dax-examples 

If this helped, Follow this blog for more insightful information about data analytics
https://analyticpulse.blogspot.com/