Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Angel
Resolver III
Resolver III

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 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,

 

 

 

 

 

 

 

 

 

1 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] )

View solution in original post

4 REPLIES 4
PattemManohar
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




@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,

 

 

 

 

 

   

 

 

 

 

 

 

 

  

 

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] )

Thanks, @AkhilAshok 

 

It works!!!!!..

 

Cooooooolll...

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.