Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a data set which can be seen in a matrix format like this: (showing in matric because it is easier to visualise that updates on stock are not regular):
Each product is receiving updates and varied frequency levels.
What I want is to calculate how many days in a period of time (e.g., a month) each product was out of stock.
The logic would go something like:
if a stock level changed on Day X to 0, or a negative number, then use this day as a first day of out of stock. If there is another record inserted for this product, and is still 0 or another negative, still use as out of stock. If this new entry is positive, count it as in stock.
Then calculate over a month how many days each product was out of stock.
Someone help, please?
Sample data:
Solved! Go to Solution.
Hi @marzwrob ,
I made simple samples and you can check the results below:
Measure = var _t = ADDCOLUMNS('Table',"Count",COUNTAX(FILTER(ALL('Table'),[ID]=EARLIER([ID])&&[Value]<=0),[Date]))
RETURN MAXX(_t,[Count])
An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Share some data to work with and show the expected result.
Hi @marzwrob ,
I made simple samples and you can check the results below:
Measure = var _t = ADDCOLUMNS('Table',"Count",COUNTAX(FILTER(ALL('Table'),[ID]=EARLIER([ID])&&[Value]<=0),[Date]))
RETURN MAXX(_t,[Count])
An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |