Forum Discussion
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...
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] )
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] )