topic Re: Cumulative total days active product in DAX Commands and Tips

Cumulative total days active product

Victornld2 — Mon, 09 Dec 2024 15:21:29 GMT

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.

Re: Cumulative total days active product

Kedar_Pande — Mon, 09 Dec 2024 16:07:43 GMT

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

Re: Cumulative total days active product

Anonymous — Tue, 10 Dec 2024 03:15:59 GMT

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

Re: Cumulative total days active product

Victornld2 — Wed, 18 Dec 2024 11:49:48 GMT

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!