Calculating stock pendancy

[lmcpinto]

Hi everyone!

Hi need help with a formula to calculate the amount of days a product was in stock. 

So i use a formula to calculate the stock so i know in a line chart how much there was based on a created date and closed date. I have something like this:

Stock of Open Complaints =

VAR OpeningStock = CALCULATE(COUNTROWS('Reclamações'), FILTER('Reclamações', 'Reclamações'[DataCriacao] <= MAX('Calendário'[Data])))

VAR NewComplaints = CALCULATE(COUNTROWS('Reclamações'), FILTER('Reclamações', 'Reclamações'[DataCriacao] > MAX('Calendário'[Data]) && 'Reclamações'[DataCriacao] <= MAX('Calendário'[Data])))

VAR ClosedComplaints = CALCULATE(COUNTROWS('Reclamações'), FILTER('Reclamações', 'Reclamações'[DataEncerramento] > MIN('Calendário'[data]) && 'Reclamações'[DataEncerramento] <= MAX('Calendário'[Data])))

RETURN OpeningStock + NewComplaints - ClosedComplaints

now i want to know the average time every reclamacao was on stock. Having example:

I know that something has datacriacao = 1/01/2023 and dataencerramento 1/03/2022. It should contribute to the average of january with one month in stock, in february with two months on stock. the time in stock should change with the time selected.

	Stock	Average Stock Pendency
Jan	3000	x
Feb	4000	x
Mar	5000	x
Apr	3000	x
May	2000	x
June	5000	x
July	6000	x

......

an example if only one complaint existed

Data criacao	data encerramento(closure)	time in stock end of january	time in stock end of february
1/01/2021	1/03/2022	30 days	60 days

and then i want to calculate the average time in stock (pendancy) for every month and year. in this case it would be 30 days in january and 60 days in february

thank you so much!

[lmcpinto]

@Anonymous  i dont think it is the correct measure. i have two tables, calendario and reclamacoes. the first one has all the dates until today and a calculated column to calculate the stock based on table reclamacoes. on the other table i have an id, date of creation and date of encerramento. this means that if a reclamacao had the data encerramento after a certain date, it was on stock on wvery dates until the encerramento. 

What i want to calculate is the average time a reclamacao is on stock for every date in the calendar.

table reclamacoes

 table calendario

thanks so much in advance!

[Anonymous]

Hi @lmcpinto ,

Due to I don't know your data model, here I create a sample to have a test.

Table:

Table(2):

Measure:

Average Stock Pendency = 
VAR _DATE =
    ADDCOLUMNS (
        CALENDAR (
            MIN ( 'Table (2)'[Data criacao] ),
            MAX ( 'Table (2)'[data encerramento(closure)] )
        ),
        "Year", YEAR ( [Date] ),
        "Month", FORMAT ( [Date], "MMM" ),
        "MonthSort", MONTH ( [Date] )
    )
VAR _DAY =
    COUNTAX (
        FILTER (
            _DATE,
            [Year] = MAX ( 'Table'[Year] )
                && [MonthSort] <= MAX ( 'Table'[MonthSort] )
        ),
        [Date]
    )
RETURN
    DIVIDE ( SUM ( 'Table'[Stock] ), _DAY )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.