DAX equivalent of GROUP BY and MIN - How do I count products launched, by year?
I want to create a measure that counts how many products were sold for the first time in a given year. That means if the product was sold in any year prior, it should not count in the current year. I would expect to do something like this: (code can be run and iterated here: [https://dax.do/fDTAmTl3kDIMaF/][1] ) DEFINE MEASURE 'Sales'[m1] = CALCULATE ( DISTINCTCOUNT (Sales[ProductKey]), SUMMARIZECOLUMNS ( Sales[ProductKey], CALCULATETABLE ( Sales, ALL( Sales[Order Date] ), ALL( 'Date'[Date] ), ALL( 'Date'[Calendar Year] ) ), "earliest_sale", MIN( Sales[Order Date] ) ), ALL( Sales[Order Date] ), ALL( 'Date'[Date] ) ) EVALUATE SUMMARIZECOLUMNS ( 'Date'[Calendar Year], "number of products sold for the first time", 'Sales'[m1] ) However, this returns the following: Which is exactly the same result as the one I get from EVALUATE SUMMARIZECOLUMNS ( 'Date'[Calendar Year], 'Sales', "distinct product sales", DISTINCTCOUNT ( Sales[ProductKey] ), "total sales", COUNTROWS ( 'Sales' ) ) Finally, going perhaps, a little bit crazy, I tried this: EVALUATE SUMMARIZECOLUMNS ( 'Date'[Calendar Year], FILTER( 'Sales', Sales[Order Date] = CALCULATE( MIN( Sales[Order Date] ), SUMMARIZE ( CALCULATETABLE ( 'Sales', ALL ( Sales[Delivery Date] ), ALL ( 'Date'[Date] ), ALL ( 'Date'[Calendar Year] ) ), Sales[ProductKey] ), ALL ( Sales[Delivery Date] ), ALL ( 'Date'[Date] ), ALL ( 'Date'[Calendar Year] ) ) ), "distinct product sales", DISTINCTCOUNT ( Sales[ProductKey] ), "sales", COUNTROWS ( 'Sales' ) ) And got: Any help would be much appreciated. I wan to count the number of products in each year that were never sold before then, i.e. were sold in that year, for the first time ever. [1]: https://dax.do/fDTAmTl3kDIMaF/
