Forum Discussion

lmcpinto's avatar
lmcpinto
Frequent Visitor
3 years ago

Calculating stock pendancy

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.
 
 
 StockAverage Stock Pendency
Jan3000x
Feb4000x
Mar5000x
Apr3000x
May2000x
June5000x
July6000x

......

 

 

an example if only one complaint existed

 

Data criacaodata encerramento(closure)time in stock end of januarytime in stock end of february
1/01/20211/03/202230 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!

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

     

  • lmcpinto's avatar
    lmcpinto
    Frequent Visitor
    3 years ago

    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!