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

[TomMartens · Accepted solution]

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

[Anonymous · Accepted solution]

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".

[TomMartens · Accepted solution]

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

[Anonymous · Accepted solution]

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".

[TomMartens · Accepted solution]

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

[Anonymous]

Thanks a lot Tom.

[TomMartens]

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

[Anonymous]

Ok.

I will try to explain with an example.

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

[TomMartens]

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