cancel
Showing results 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

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

1 ACCEPTED SOLUTION
Solution Sage

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] )```
4 REPLIES 4
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.

Proud to be a PBI Community Champion

Resolver III

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,

Solution Sage

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] )```
Resolver III

Thanks, @AkhilAshok

It works!!!!!..

Cooooooolll...

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors