Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
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
User | Count |
---|---|
107 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |