Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.