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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.