Calculate duration of a promotion

[dianis]

Dear community, 

I am looking to build a measure that allows me to calculate the average duration of a promotion, based on the parameters below:

cod_tienda is the store code

cod_semana is the week the promotion was active

cod_producto is product code

promotion is the promotion type

What I am looking for is to have a new column, that counts the number of weeks a promotion was active on that specific store and product. For instance:

- For Index 1, promotion was active for 1 week

- For indexes 4 to 7, the promotion was active for 4 weeks

- For Index 8, promotion was active for 1 week

- For Indexes 9 to 10, promotion was active for 2 weeks... and so one

At the end I want to understand what the average duration for a promotion is for an specific store and product.

Any hint on how to move forward?

Thanks!

[v-yiruan-msft · Accepted solution]

Hi @dianis ,

You can create a calculated column as below to get it, please find the details in the attachment.

Average duration = 
VAR _minweek =
    CALCULATE (
        MIN ( 'Table'[cod_semana] ),
        FILTER (
            'Table',
            'Table'[cod_tienda] = EARLIER ( 'Table'[cod_tienda] )
                && 'Table'[cod_producto] = EARLIER ( 'Table'[cod_producto] )
                && 'Table'[promocion] = "Promo descuento"
        )
    )
VAR _maxweek =
    CALCULATE (
        MAX ( 'Table'[cod_semana] ),
        FILTER (
            'Table',
            'Table'[cod_tienda] = EARLIER ( 'Table'[cod_tienda] )
                && 'Table'[cod_producto] = EARLIER ( 'Table'[cod_producto] )
                && 'Table'[promocion] = "Promo descuento"
        )
    )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[cod_semana] ),
        FILTER (
            'Table',
            'Table'[cod_tienda] = EARLIER ( 'Table'[cod_tienda] )
                && 'Table'[cod_producto] = EARLIER ( 'Table'[cod_producto] )
        )
    )
RETURN
    IF ( _count = 1, 1, ( _maxweek - _minweek + 1 ) / _count )

Best Regards