topic Re: Counting unique number of products that were on offer in the past and are now in DAX Commands and Tips

Counting unique number of products that were on offer in the past and are now

Faustyna — Mon, 10 Oct 2022 12:14:11 GMT

Hi!

I need to count unique number of products that were in the assortment, for example, in January and are now. I can't do normally: calculate(DISTINCTCOUNT(Assort[ProductId]),month(Assort[AssortDate])=1), because I will count SKUs that were in the assortment in January, and I wanted to count those that were in January and are still there.

Does anyone have any idea how to count it?I would like to get a table that will show by months the number of products that were on sale in January and are still sold in each month.

I would be grateful for your help

Faustina

Re: Counting unique number of products that were on offer in the past and are now

johnt75 — Mon, 10 Oct 2022 13:23:10 GMT

Try creating a measure like

Still on sale = VAR JanProducts = CALCULATETABLE ( VALUES ( Assort[ProductId] ), MONTH ( Assort[AssortDate] ) = 1 ) VAR CurrentMonth = MONTH ( MAX ( 'Date'[Date] ) ) VAR CurrentProducts = CALCULATETABLE ( VALUES ( Assort[ProductId] ), MONTH ( Assort[AssortDate] ) = CurrentMonth ) RETURN COUNTROWS ( INTERSECT ( CurrentProducts, JanProducts ) )

Re: Counting unique number of products that were on offer in the past and are now

Faustyna — Mon, 17 Oct 2022 06:44:01 GMT

Thank you so much for a help!!