Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a measure for counting days of a product without stock that I wrote and is calculating OK. It goes like this: (sample file attached)
Zero Stock Days =
COUNTROWS (
FILTER (
ADDCOLUMNS (
SUMMARIZE (
FILTER (
'Calendar',
AND (
'Calendar'[Date] >= MIN ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
),
'Calendar'[Date],
"Any Stock", MAX ( moves[Stock] ),
"Helper field", [INDEX w/o stock]
),
"Final count",
SWITCH (
TRUE (),
[Any Stock] > 0, 1,
ISBLANK ( [Any Stock] )
&& [Helper field] > 0, 1,
[Helper field]
)
),
[Final count] = 0
)
)
The problem is that the measure is too slow. My goal is to count the Zero stock days in the range of days selected, for example "out of 43 selected days the product was out of stock for 7 days".
The measure has to take into account some specific situations that you can see below:
a) If I select the date range starting 30.3.2023 and ending 16.5.2023, the measure has to recognize that since 6.3.2023 (which is before the selected range) the sample1 product was out of stock until 6.4.2023, so in my range it was 7 days out of stock (starting 30.3.2023 as my selected start date and ending 5.4.2023) - highlighted in red
b) Another situation is when the last move of the day (recognized by the index column in column D as there can be more than one moves in one day) depleted the stock to 0, then the next day is also a zero until there is another record with nonzero stock. This happened on 14.4.2023 to sample2 and it was not in stock until 24.4.2023 - highlighted in green
As I already wrote, the measure works fine but I would be very grateful if someone could help me make it faster performance wise. I subsequently use this measure in another measures and the visual takes ages to load.
I attached the sample file here: Sample file
There is another important measure "INDEX w/o stock" that calculated the amount of stock before every given day.
Big thanks in advance to anyone who can direct me to speed up the calculation 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |