Forum Discussion

lmcpinto's avatar
lmcpinto
Frequent Visitor
3 years ago

DAX formula help!

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.
 
my idea is to put it on a line chart with year and month.
 
thank you so much in advance!

 

 

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    lmcpinto Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • lmcpinto's avatar
      lmcpinto
      Frequent Visitor
      3 years ago
       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

       

      Greg_Deckler 

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        3 years ago

        lmcpinto OK, so I am not understanding how to get from your sample table of data to your expected output. It looks like you have a Month Column and a Stock column but I don't understand the "x"'s in the Average Stock Pendency column. Is that the average for all the rows in the table? The average for YTD? Or maybe something else? I also don't understand how you get to your second table that you posted. Is there a product involved here? What is in stock for 30 days, 60 days? I just don't understand what is going on here. If you can post a link to a sample PBIX file that might help tremendously.