Forum Discussion

Angel's avatar
Angel
Resolver III
7 years ago
Solved

DAX formula help

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 cod...
Need Help
  • AkhilAshok's avatar
    AkhilAshok
    7 years ago

    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's avatar
Angel
Resolver III
7 years ago

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's avatar
AkhilAshok
Solution Sage
7 years ago

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's avatar
    Angel
    Resolver III
    7 years ago

    Thanks, AkhilAshok 

     

    It works!!!!!..

     

    Cooooooolll...