cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors