topic Re: DAX formula help! in DAX Commands and Tips

DAX formula help!

lmcpinto — Tue, 11 Apr 2023 13:56:57 GMT

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!

Re: DAX formula help!

Greg_Deckler — Tue, 11 Apr 2023 14:29:10 GMT

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.

Re: DAX formula help!

lmcpinto — Tue, 11 Apr 2023 14:59:31 GMT

	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

Greg_Deckler

Re: DAX formula help!

Greg_Deckler — Thu, 13 Apr 2023 15:50:50 GMT

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.