Forum Discussion

Thigs's avatar
Thigs
Helper IV
4 years ago
Solved

SKU Distribution Trend?

Hi all! I'm working with a client with about 4,000 SKUs in a given year. However, it is a very seasonal business, so only about 1,500 SKUs are in store at any given time. What I need to know is how ...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Thigs 

    This is only part of the complete measure

    Matching Percent =
VAR CurrentSKUs =
    VALUES ( 'SKU Distributions'[SKU] )
VAR PreviousSKUs =
    CALCULATETABLE (
        VALUES ( 'SKU Distributions'[SKU] ),
        'SKU Distributions'[Date Only]
            = MAX ( 'SKU Distributions'[Date Only] ) - 1
    )
RETURN
    COUNTROWS ( INTERSECT ( PreviousSKUs, CurrentSKUs ) )
Thigs's avatar
Thigs
Helper IV
4 years ago

No worries, thank you SO much for your help!

 

Matching Percent = CALCULATETABLE(VALUES('SKU Distributions'[SKU]),'SKU Distributions'[Date Only] = MAX('SKU Distributions'[Date Only]) -1)

SKU is simply a list of SKUs. Date Only is a list of dates (with the time stamp removed). Should it be a calculated column? I figured it was a measure since each row in the table is not one date - each row is a date + a sku. 

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Thigs 

This is only part of the complete measure

Matching Percent =
VAR CurrentSKUs =
    VALUES ( 'SKU Distributions'[SKU] )
VAR PreviousSKUs =
    CALCULATETABLE (
        VALUES ( 'SKU Distributions'[SKU] ),
        'SKU Distributions'[Date Only]
            = MAX ( 'SKU Distributions'[Date Only] ) - 1
    )
RETURN
    COUNTROWS ( INTERSECT ( PreviousSKUs, CurrentSKUs ) )
  • Thigs's avatar
    Thigs
    Helper IV
    4 years ago

    Ok, so if I'm reading this correctly, this gives me the number of SKUs that are the same as the previous day. Then I need a percent, so I would divide by PreviousSKUs? Or do I need a new variable?

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Yes no need for new measure 

  • Thigs's avatar
    Thigs
    Helper IV
    4 years ago

    I tried this this morning - it is back to giving me the error about not expecting multiple values. Here's what I changed the measure to (old one was working perfectly)

     

    Matching Percent =
    VAR CurrentSKUs =
    VALUES ( 'SKU Distributions'[SKU] )
    VAR PreviousSKUs =
    CALCULATETABLE (
    VALUES ( 'SKU Distributions'[SKU] ),
    'SKU Distributions'[Date Only]
    = MAX ( 'SKU Distributions'[Date Only] ) - 1
    )

    VAR NumRows = COUNTROWS ( INTERSECT ( PreviousSKUs, CurrentSKUs ) )
    RETURN
    DIVIDE(NumRows, PreviousSKUs)
  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    You missed the COUNTROWS of "PreviousSKUs 

  • Thigs's avatar
    Thigs
    Helper IV
    4 years ago

    Like this?

    Matching Percent 2 =
    VAR CurrentSKUs =
    VALUES ( 'SKU Distributions'[SKU] )
    VAR PreviousSKUs =
    CALCULATETABLE (
    VALUES ( 'SKU Distributions'[SKU] ),
    'SKU Distributions'[Date Only]
    = MAX ( 'SKU Distributions'[Date Only] ) - 1
    )

    VAR NumRows = COUNTROWS ( INTERSECT ( PreviousSKUs, CurrentSKUs ) )
    RETURN
    DIVIDE(NumRows, COUNTROWS(PreviousSKUs))
     
    It just returns "1" for everything....
  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Can you share a screenshot?