cancel
Showing results for
Did you mean:
Frequent Visitor

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

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

1 ACCEPTED SOLUTION
Super User

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 ) )
``````
2 REPLIES 2
Super User

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 ) )
``````
Frequent Visitor

Thank you so much for a help!!

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors