Find and Count Distinct Instances of Minimum Dates by Group

[VBAmazing]

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:

[Greg_Deckler]

@VBAmazing Any chance you can post sample data? It's a complex calculation so would be far easier to mock something up. I am wondering if you are falling victim to CALCULATE's potential wonkiness in situations like this, https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Better-Running-Total/m-p/2755666#M8...

or whether you are going to need to do something similar to Open Tickets, https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

[VBAmazing]

I am - frustruatingly - unable to directly upload the .PBIX file as the forum is saying the file type is not supported.

Here is a link to the file on Drive. The .PBIX file is using the Contoso dataset -- so no sensitive information. 

Thanks for the quick reply. 

https://drive.google.com/file/d/1nCNHBSbL5meRWw3TGhhEDA_GujgZlHMk/view?usp=sharing

[Anonymous]

Thanks for the reply from Greg_Deckler , please allow me to provide another insight:

Hi, @VBAmazing 

Due to our current security protocols, we are temporarily unable to access the data provided by Google.

If you require our assistance, I suggest uploading the data using the link provided below:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

When uploading a file, please be careful to delete sensitive information.

I look forward to hearing from you.

Best Regards,

Leroy Lu