Maximum/Average consecutive number of days that a value drops below zero

[Anonymous]

Hi guys, 

currently I'm working on a detailled inventory report. 

Basically, my data consists of two tables:

- Table A: Information about the stock level s of product p at company c at date d

Date	Product	Company	Stock
01-01-2020	x	Amazon	100
02-01-2020	x	Amazon	40
03-01-2020	x	Amazon	60
01-01-2020	y	Walmart	70

- Table B: Information about the demand r of product p at companyc at date d.

Date	Product	Company	DemandId	Quantity
01-01-2020	x	Amazon	1	50
01-01-2020	x	Amazon	2	40
02-01-2020	y	Amazon	3	60

Of course I could add the 'Demand' as additional column for table A, however I want to have all the calculations done dynamically so I can filter out certain types of demand. 

First of all, I created the net-stock per date, product and company. Basically, this is just an easy measure: 

NetStock := SUM(Stock) - SUM(Quantity)

Next to that, I want to create a KPI that measures the number of occassions that the stock drops below 0 (and we are basically out of stock). As aggregating all the products and companies into one sum does not work, I decided to determine this on the lowest level:

StockOutOccasions = 
var JoinedTable =
SUMMARIZE(
    'Table_A';
    'Table_A'[Company];
    'Table_A'[Product];
    'Table_A'[Date];
    "OutOfStock"; IF([NetStock] < 0; 1; 0)
)
return SUMX(JoinedTable; [OutOfStock])

Similair measures have been created to measure the stock deficit, probability of stock-out etc.

My final KPI is to represent the duration of a stock-out, both the maximum and average duration. 

However, I'm quite struggling how to do this, as I use a measured table in previous calculations.

My expectations are for the following measured table:

Date	Product	Company	NetStock
01-01	x	y	-10
02-01	x	y	-15
03-01	x	y	-10
04-01	x	y	5
05-01	x	y	-20
06-01	x	y	10

Maximum duration: 3 (=> 01-01 untill 03-01)

Average duration: 2 (=> (3+1)/2 )

I had the idea to create an additional 'column' which indicates the number of consecutive days that the netstock < 0, and 0 if the netstock >= . This would result in the following:

Date	01-01	02-01	03-01	04-01	05-01	06-01
x	1	2	3	0	1	0

Then the maxium duration would just be the maximum value of this column.

The average can be determined as: COUNT(x>0)/COUNT(x=1)

So, the idea is there, I tried a couple of things, but I'm mainly stuck on the restrictions of the measured table. 

Can anybody help me how to create the column x? (See the last matrix)

I am aware that the measure is probably going to be somewhat slow, however I prefer this since this allows me to use filters.

Thanks in advance!

[TomMartens]

Hey @Anonymous ,

maybe this post will provide what you are looking for or at least provides some new ideas: 

https://community.powerbi.com/t5/Desktop/DAX-formula-for-consecutive-events-with-some-criteria/m-p/861009#M413119

If not, please create a pbix file that contains some sample data but still reflects your data model, upload the file to onedrive or dropbox and share the link.

Make sure that the pbix has a proper data model (star schema), meaning that there are dedicated tables for company, product, date, (not sure about the demandid).

Regards,

Tom

[Anonymous]

HI @Anonymous,

Measure formula is calculated based on its row contents, so it can be dynamically affected by filter/slicers.
When you use it in a calculated column/table, its row contents level will be fixed as whole table and it not able to interact with the slicer/filter to achieve a dynamic calculated column/table.

BTW, measure formulas not able to use as a category to group and aggregate records.
Regards,

Xiaoxin Sheng

[amitchandak]

@ImkeF , can you help

[ImkeF]

Hi Amit,

I wish I could help here, but that's beyond my DAX skills today.

A nested / conditional index in DAX needs some DAX superpowers from @Greg_Deckler , @TomMartens  or @OwenAuger .