DAX formula help

[Angel]

Hi, everybody

I hope someone can help me...

I have a sales table with this columns: Product code, Sales date, Quantity

I want to create a Table in Power BI with these columns: Product code, Quantity, Month with more quantity sales by product, Last Sales date by product. 

The key is I don't want filters affect last two columns.. I suppose I can do that using DAX... 

Can anyone help me??

Thanks in advance,

[PattemManohar]

@Angel  Please post sample test data and expected output which will be helpful to understand in detail and to suggest an accurate solution.

[Angel]

@PattemManohar 

Ok...  Sales Table  data is something like this

    Product         Sales Date             Quantity

    ---------         -----------             ---------

    Bike                15/01/2019                10

    Car                 17/02/2019                12

    Bike               18/02/2019                   7

    Car                19/02/2019                 12

    Bike               17/01/2019                   7

    Car                11/01/2019                   3

    Car                15/03/2019                   1

The expected  output will be:

    Product             Number of Sales          Total Quantity       Last Sales Date       Month with more sales

    ---------            -------------------         ---------------        ----------------       --------------------------

    Bike                    3                                   24                        18/02/2019            01 (January)

    Car                     4                                   26                         15/03/2019           02 (February)

'Number of sales'  and 'Total Quantity' must be affected by filters users have set up. 

'Last Sales Date' and 'Month with more sales' don't have to be affected by the filters

Thanks,

[AkhilAshok · Accepted solution]

Does somehting like this work for you:

• A calculated column for getting the Sale Month. If you have the attribute in Date table, then you can ignore this.

Sale Month =
FORMAT ( MONTH ( 'Table'[Sale Date] ), "0#" ) & " ("
    & FORMAT ( 'Table'[Sale Date], "Mmmm" ) & ")"

• Create the following measures: Here the ALLEXCEPT function will clear filter applied on all dimensions except Product.

Number of Sales = COUNTROWS('Table')

Total Quantity = SUM('Table'[Quantity])

Last Sale Date = 
CALCULATE (
    MAX ( 'Table'[Sale Date] ),
    ALLEXCEPT ( 'Table', 'Table'[Product] )
)

Month with more Sales = 
VAR MonthlySales =
    CALCULATETABLE (
        ADDCOLUMNS ( VALUES ( 'Table'[Sale Month] ), "Sale", [Number of Sales] ),
        ALLEXCEPT ( 'Table', 'Table'[Product] )
    )
VAR TopSale =
    TOPN ( 1, MonthlySales, [Sale], DESC )
RETURN
    MAXX ( TopSale, 'Table'[Sale Month] )

[Angel]

Thanks, @AkhilAshok 

It works!!!!!..

Cooooooolll...