cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Faustyna
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
johnt75
Super User
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 ) )

View solution in original post

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

Thank you so much for a help!!

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors