Find and Count Distinct Instances of Minimum Dates by Group
Hello Forum, I am working inside a Semantic Model that uses a star schema (construction is as-expected, many:one relationship between all Fact and Dimension Tables). I have a requirement to count the distinct instances of the first Order Date across several dimensions from my dimension tables. **NOTE THIS IS IN A SEMANTIC MODEL -- NEW COLUMNS / TABLES / POWER QUERY MANIPULATIONS ARE NOT POSSIBLE** Business Case: Aggregating New Placements of a Particular Product. New Placements: The first instance of a Product Group being shipped to a Customer at a specific Location in the same year as the maximum date in the Context. Product, Customer and Location are three of my dimension tables. While I have been able to aggregate this successfully (Return the correct number of New Placements as a single row) I am unable to plot this correctly across months. I have tried the following: CALCULATION: __NewPlacements = var maxContextDate = CALCULATE( MAX( Dates[DateKey] ), ALLSELECTED( 'Sales' ) ) RETURN CALCULATE( CALCULATE( COUNTROWS( SUMMARIZE( sales, 'Product'[ClassName], Stores[StoreName], "Min_Delivery_Date", MIN(Sales[DateKey]) ) ), FILTER( SUMMARIZE( FILTER( Sales, Sales[SalesQuantity] > 0 ), 'Product'[ClassName], Stores[StoreName], "Min_Delivery_Date", MIN(Sales[DateKey]) ), [Min_Delivery_Date] >= DATE( YEAR( maxContextDate ), 1, 1) && [Min_Delivery_Date] <= maxContextDate && 'Product'[ClassName]= "Regular" ) ), REMOVEFILTERS( Dates[DateKey] ) ) Which Produces this -- 306 is the correct number, but plotted against months of year it is nonsensical DAX STUDIO: // EVALUATES TO CORRECT NUMBER BUT DUPLICATED ACROSS MONTHS // NEED TO DETERMINE DISTINCT NEW PLACEMENTS IN EACH MONTH EVALUATE // FILTERED FACT TABLE VAR filteredSales = FILTER( Sales, RELATED( 'Product'[ClassName] ) = "Regular" && Sales[SalesQuantity] > 0 ) // SUMMARIZE BY DIMENSIONS NEEDED VAR basicCalc = SUMMARIZE( filteredSales, 'Product'[ClassName], Stores[StoreName], "New Placement Month", DATE( YEAR( MIN( Dates[DateKey] ) ), MONTH( MIN( Dates[DateKey] ) ), 1 ) ) VAR basicCalc_Monthly = SUMMARIZE( basicCalc, [New Placement Month], "Rows", COUNTROWS( basicCalc ) ) VAR filteredBasicCalc = FILTER( basicCalc, YEAR( [New Placement Month] ) = 2011 ) VAR CalculatedNewPlacements = CALCULATE( COUNTROWS( filteredBasicCalc ) ) VAR table_CalculatedNewPlacements = ROW( "Calculated New Placements", CalculatedNewPlacements ) RETURN basicCalc_Monthly And this produces the same number for each month, again not working as expected:
