cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

I'm new to DAX. Want to know how to get the best sales and previous best sales?

3 ACCEPTED SOLUTIONS
Super User

Hey @Anonymous ,

not sure if I understand correctly as there is also a red box 😞

Nevertheless, this is my sample data:

I create three measures that allow to create these three card visuals:

Here are the measures:
Top 1 sale:

``````Top 1 sale =
var top1salserows =
SELECTCOLUMNS(
TOPN(
1
, ALLSELECTED( 'Sales Table' )
, 'Sales Table'[sales]
, DESC
)
, "top" ,  'Sales Table'[sales]
)
return
top1salserows``````

2nd best sales:

``````Top 2 sale =
var top2salserows =
minx(
TOPN(
2
, ALLSELECTED( 'Sales Table' )
, 'Sales Table'[sales]
, DESC
)
, 'Sales Table'[sales]
)
return
top2salserows``````

previous best sale:

``````previous bestsale =
var top1salesdate =
SELECTCOLUMNS(
TOPN( 1 , ALLSELECTED( 'Sales Table' ) , CALCULATE(SUM( 'Sales Table'[sales] ) ) , desc )
, [date]
)
var prevSales =
maxx(
filter(
allselected( 'sales table' )
, 'sales table'[date] < top1salesdate
)
, [sales]
)
return

prevSales``````

Hopefully, this provides what you are looking for!

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Thanks! Tom,

This solved the problem to a great extent.

Now the corresponding dates are to be calculated. Not able to get the "previous best sale date".

Super User

Hey @Anonymous ,

here are the measures to calculate the dates.

Top 1 best sales date:

``````Top 1 sales date =
var top1salserows =
SELECTCOLUMNS(
TOPN(
1
, ALLSELECTED( 'Sales Table' )
, 'Sales Table'[sales]
, DESC
)
, "topdate" ,  'Sales Table'[date]
)
return
top1salserows``````

Top 2 sales date:

``````Top 2 sales date =
var top2salserows =
SELECTCOLUMNS(
TOPN(
1
, TOPN(
2
, ALLSELECTED( 'Sales Table' )
, 'Sales Table'[sales]
, DESC
)
, 'Sales Table'[sales]
, ASC
)
, "date"  , [date]
)
return
top2salserows``````

Previous best sales date:

``````previous bestsales date =
var top1salesdate =
SELECTCOLUMNS(
TOPN( 1 , ALLSELECTED( 'Sales Table' ) , CALCULATE(SUM( 'Sales Table'[sales] ) ) , desc )
, [date]
)
var prevSales =
SELECTCOLUMNS(
TOPN(
1
, filter(
allselected( 'sales table' )
, 'sales table'[date] < top1salesdate
)
, [sales]
, DESC
)
, "date" , [date]
)
return
prevSales``````

The report now looks like this:

Hopefully, this provides what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
7 REPLIES 7
Super User

Hey @Anonymous ,

not sure if I understand correctly as there is also a red box 😞

Nevertheless, this is my sample data:

I create three measures that allow to create these three card visuals:

Here are the measures:
Top 1 sale:

``````Top 1 sale =
var top1salserows =
SELECTCOLUMNS(
TOPN(
1
, ALLSELECTED( 'Sales Table' )
, 'Sales Table'[sales]
, DESC
)
, "top" ,  'Sales Table'[sales]
)
return
top1salserows``````

2nd best sales:

``````Top 2 sale =
var top2salserows =
minx(
TOPN(
2
, ALLSELECTED( 'Sales Table' )
, 'Sales Table'[sales]
, DESC
)
, 'Sales Table'[sales]
)
return
top2salserows``````

previous best sale:

``````previous bestsale =
var top1salesdate =
SELECTCOLUMNS(
TOPN( 1 , ALLSELECTED( 'Sales Table' ) , CALCULATE(SUM( 'Sales Table'[sales] ) ) , desc )
, [date]
)
var prevSales =
maxx(
filter(
allselected( 'sales table' )
, 'sales table'[date] < top1salesdate
)
, [sales]
)
return

prevSales``````

Hopefully, this provides what you are looking for!

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Thanks! Tom,

This solved the problem to a great extent.

Now the corresponding dates are to be calculated. Not able to get the "previous best sale date".

Super User

Hey @Anonymous ,

here are the measures to calculate the dates.

Top 1 best sales date:

``````Top 1 sales date =
var top1salserows =
SELECTCOLUMNS(
TOPN(
1
, ALLSELECTED( 'Sales Table' )
, 'Sales Table'[sales]
, DESC
)
, "topdate" ,  'Sales Table'[date]
)
return
top1salserows``````

Top 2 sales date:

``````Top 2 sales date =
var top2salserows =
SELECTCOLUMNS(
TOPN(
1
, TOPN(
2
, ALLSELECTED( 'Sales Table' )
, 'Sales Table'[sales]
, DESC
)
, 'Sales Table'[sales]
, ASC
)
, "date"  , [date]
)
return
top2salserows``````

Previous best sales date:

``````previous bestsales date =
var top1salesdate =
SELECTCOLUMNS(
TOPN( 1 , ALLSELECTED( 'Sales Table' ) , CALCULATE(SUM( 'Sales Table'[sales] ) ) , desc )
, [date]
)
var prevSales =
SELECTCOLUMNS(
TOPN(
1
, filter(
allselected( 'sales table' )
, 'sales table'[date] < top1salesdate
)
, [sales]
, DESC
)
, "date" , [date]
)
return
prevSales``````

The report now looks like this:

Hopefully, this provides what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Thanks a lot Tom.

Super User

Hey @Anonymous ,

if one or both of my posts answers your question, mark one or both posts as an answer. This will help other users to find a solution more quickly.

Regards,
Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Ok.

I will try to explain with an example.

What I'm interested is the four results in blue boxes.

Super User

Hey @Anonymous ,

mastering DAX starts with a clear description of what you want to achieve, what has to be calculated.

This:  "... best sales and previous best sales ..."

Does not reveal anything in regards to business requirements/business rules, as long you provide more context, what defines "best" and "previous best."

Regards,
Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany