Cumulative total days active product

[Victornld2]

Hi all,

Been struggeling with this challenge: 

Per month, I need to see the average of active days per product. 

Example, 

1 product gets active on 1-jan-2024, and gets deactivated on 1-oct-2024. 

So on 10-jan-2024 the average of active days = 10, on 30-9-2024, the average of active days  = 274. On 1-10-2024 its NULL for this product. 

This is for 1 product, but it needs to aggregate for all products active on that day.

For the convenience I mad a fact table (fact_ActiveProducts) with 1 row per product per active day. 

fact_ActiveProducts, columns:

ProductID

,DateID (related with Calendar table)

,BeginDate (date format)

,EndDate (dateformat) (can be empty when still active). 

,NumberOfActiveDays (1, a hard number 1 on each row to easy SUM the column).

Anybody got suggestions? The usual ways to calculate a rolling sum of cumulative total still counts the days of products after they where deactivated. And after the date that they are deactivated, they shouldn't be counted anymore for those dates.

If I get the total count of days per row, I can divide it by a distinctcount of products active on that day.

The next step is to create an average by month, or get the value of the maxdate for the selected month.

[Victornld2 · Accepted solution]

Apologies for the late reply.

The advised solution didn't work. 

Ultimately we came up with the following solution:

Average active days per product =

VAR _result = SUMX(

                    VALUES( fact_ActiveProducts[ProductID] ) ,

                        CALCULATE(

                            SUM( fact_ActiveProducts[NumberOfProductsStarting] ) ,

                                FILTER(

                                    ALL( Calendar[Date] ) ,

                                    Calendar[Date] <= MAX( Calendar[Date] ) &&

                                    Calendar[Date] <= CALCULATE( MAX( fact_ActiveProducts[Date] ) , ALL( Calendar[Date] ) )

                                    )

                        )

                )

RETURN

    DIVIDE( _result , [Count of active products] , BLANK() )

Again, my apologies for the late reaction but thank you very much for the effort and suggested advice!

[Anonymous]

Hi @Victornld2 ,

Whether the advice given by Kedar_Pande has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Best Regards,

Neeko Tang

[Kedar_Pande]

@Victornld2 

Ensure your Calendar table has a relationship with fact_ActiveProducts[DateID]

Create measure:

AvgActiveDaysPerProduct =
VAR ActiveProducts =
CALCULATETABLE(
VALUES(fact_ActiveProducts[ProductID]),
fact_ActiveProducts[DateID] <= MAX('Calendar'[Date]),
ISBLANK(fact_ActiveProducts[EndDate]) || fact_ActiveProducts[EndDate] >= MAX('Calendar'[Date])
)
VAR TotalActiveDays =
CALCULATE(
SUM(fact_ActiveProducts[NumberOfActiveDays]),
VALUES(fact_ActiveProducts[ProductID]),
fact_ActiveProducts[DateID] <= MAX('Calendar'[Date]),
ISBLANK(fact_ActiveProducts[EndDate]) || fact_ActiveProducts[EndDate] >= MAX('Calendar'[Date])
)
VAR DistinctProducts =
COUNTROWS(ActiveProducts)
RETURN
IF(
DistinctProducts > 0,
TotalActiveDays / DistinctProducts,
BLANK()
)

AvgActiveDaysPerProductByMonth =
AVERAGEX(
VALUES('Calendar'[Date]),
[AvgActiveDaysPerProduct]
)

Add Calendar[Month] or Calendar[Date] to your visual.
Use [AvgActiveDaysPerProduct] or [AvgActiveDaysPerProductByMonth] as the value field.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn