Forum Discussion
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,
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
Angel Please post sample test data and expected output which will be helpful to understand in detail and to suggest an accurate solution.
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] )
