Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello, good afternoon,
I have a calculated table with which I get in a column named available if a product was available in a store on a certain date as shown in the following image.
What I intend to obtain are the consecutive days in which the available column was zero, since with this I obtain the number of consecutive days that a product was out of stock in a certain store, I have already tried it in several ways but I cannot find how to achieve this , I hope you can help me
Solved! Go to Solution.
Hi @Yeztrom ,
According to your description, I download your pbix file. As your data is too large, I think create a new calculated table is not a good solution, it will lead the report to run too slow.
Here's my solution.
Create two columns.
Period =
VAR _Start =
MAXX (
FILTER (
'vw_product_availabilies',
'vw_product_availabilies'[master_date_id]
< EARLIER ( 'vw_product_availabilies'[master_date_id] )
&& 'vw_product_availabilies'[product_available] = 1
&& 'vw_product_availabilies'[retailer_id]
= EARLIER ( vw_product_availabilies[retailer_id] )
&& 'vw_product_availabilies'[master_product_id]
= EARLIER ( vw_product_availabilies[master_product_id] )
),
'vw_product_availabilies'[master_date_id]
)
VAR _End =
MINX (
FILTER (
'vw_product_availabilies',
'vw_product_availabilies'[master_date_id]
> EARLIER ( 'vw_product_availabilies'[master_date_id] )
&& 'vw_product_availabilies'[product_available] = 1
&& 'vw_product_availabilies'[retailer_id]
= EARLIER ( vw_product_availabilies[retailer_id] )
&& 'vw_product_availabilies'[master_product_id]
= EARLIER ( vw_product_availabilies[master_product_id] )
),
'vw_product_availabilies'[master_date_id]
)
VAR _Start2 =
IF (
_Start = BLANK (),
MIN ( 'vw_product_availabilies'[master_date_id] ),
MINX (
FILTER (
'vw_product_availabilies',
'vw_product_availabilies'[retailer_id]
= EARLIER ( vw_product_availabilies[retailer_id] )
&& 'vw_product_availabilies'[master_product_id]
= EARLIER ( vw_product_availabilies[master_product_id] )
&& 'vw_product_availabilies'[master_date_id] > _Start
),
'vw_product_availabilies'[master_date_id]
)
)
VAR _End2 =
IF (
_End = BLANK (),
MAX ( 'vw_product_availabilies'[master_date_id] ),
MAXX (
FILTER (
'vw_product_availabilies',
'vw_product_availabilies'[retailer_id]
= EARLIER ( vw_product_availabilies[retailer_id] )
&& 'vw_product_availabilies'[master_product_id]
= EARLIER ( vw_product_availabilies[master_product_id] )
&& 'vw_product_availabilies'[master_date_id] < _End
),
'vw_product_availabilies'[master_date_id]
)
)
RETURN
IF (
'vw_product_availabilies'[product_available] = 1,
BLANK (),
_Start2 & "-" & _End2
)
days = IF('vw_product_availabilies'[Period]=BLANK(),BLANK(),1)
The days column will add up in the visual.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Yeztrom ,
According to your description, I download your pbix file. As your data is too large, I think create a new calculated table is not a good solution, it will lead the report to run too slow.
Here's my solution.
Create two columns.
Period =
VAR _Start =
MAXX (
FILTER (
'vw_product_availabilies',
'vw_product_availabilies'[master_date_id]
< EARLIER ( 'vw_product_availabilies'[master_date_id] )
&& 'vw_product_availabilies'[product_available] = 1
&& 'vw_product_availabilies'[retailer_id]
= EARLIER ( vw_product_availabilies[retailer_id] )
&& 'vw_product_availabilies'[master_product_id]
= EARLIER ( vw_product_availabilies[master_product_id] )
),
'vw_product_availabilies'[master_date_id]
)
VAR _End =
MINX (
FILTER (
'vw_product_availabilies',
'vw_product_availabilies'[master_date_id]
> EARLIER ( 'vw_product_availabilies'[master_date_id] )
&& 'vw_product_availabilies'[product_available] = 1
&& 'vw_product_availabilies'[retailer_id]
= EARLIER ( vw_product_availabilies[retailer_id] )
&& 'vw_product_availabilies'[master_product_id]
= EARLIER ( vw_product_availabilies[master_product_id] )
),
'vw_product_availabilies'[master_date_id]
)
VAR _Start2 =
IF (
_Start = BLANK (),
MIN ( 'vw_product_availabilies'[master_date_id] ),
MINX (
FILTER (
'vw_product_availabilies',
'vw_product_availabilies'[retailer_id]
= EARLIER ( vw_product_availabilies[retailer_id] )
&& 'vw_product_availabilies'[master_product_id]
= EARLIER ( vw_product_availabilies[master_product_id] )
&& 'vw_product_availabilies'[master_date_id] > _Start
),
'vw_product_availabilies'[master_date_id]
)
)
VAR _End2 =
IF (
_End = BLANK (),
MAX ( 'vw_product_availabilies'[master_date_id] ),
MAXX (
FILTER (
'vw_product_availabilies',
'vw_product_availabilies'[retailer_id]
= EARLIER ( vw_product_availabilies[retailer_id] )
&& 'vw_product_availabilies'[master_product_id]
= EARLIER ( vw_product_availabilies[master_product_id] )
&& 'vw_product_availabilies'[master_date_id] < _End
),
'vw_product_availabilies'[master_date_id]
)
)
RETURN
IF (
'vw_product_availabilies'[product_available] = 1,
BLANK (),
_Start2 & "-" & _End2
)
days = IF('vw_product_availabilies'[Period]=BLANK(),BLANK(),1)
The days column will add up in the visual.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
there might be a better way to do this, but here is one option:
first create this calculated column, which identifies the first day in a group of consecutive days:
FirstDateOfGroup =
VAR _t1 =
CALCULATE (
MAX ( stock[Date] ),
FILTER (
ALL ( stock ),
stock[retailer_id] = EARLIER ( stock[retailer_id] )
&& stock[product_id] = EARLIER ( stock[product_id] )
&& stock[Available] <> EARLIER ( stock[Available] )
&& stock[Date] < EARLIER ( stock[Date] )
)
)
VAR _t2 =
CALCULATE (
MIN ( stock[Date] ),
FILTER (
ALL ( stock ),
stock[retailer_id] = EARLIER ( stock[retailer_id] )
&& stock[product_id] = EARLIER ( stock[product_id] )
&& stock[Available]
== EARLIER ( stock[Available] )
&& stock[Date] <= EARLIER ( stock[Date] )
)
)
RETURN
IF ( ISBLANK ( _t1 ), _t2, _t1 + 1 )
then this calculated column to count the number of days within each group:
NumberOfConsecutiveDaysOutOfStock =
IF (
stock[Available] = 0,
COUNTROWS (
FILTER (
ALL ( stock ),
stock[retailer_id] = EARLIER ( stock[retailer_id] )
&& stock[product_id] = EARLIER ( stock[product_id] )
&& stock[Available]
== EARLIER ( stock[Available] )
&& stock[FirstDateOfGroup] = EARLIER ( stock[FirstDateOfGroup] )
)
),
BLANK ()
)
the rest of your requirements should be fairly easy to obtain based on these two columns.
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
Hello good afternoon, thanks for the support,
I have one more doubt, is it possible to put all the calculations in a single measure?
All this calculation is going to be used inside a measure that is going to be shown in the dashboard, it is going to show in the return the functions max, min and several things apart, the reason why it is done this way is because the measure has to be dynamic and the result shown has to vary depending on the selected filters.
I tried to put all the calculations into one measurement but I got this error
The measure is:
Hello good afternoon, thank you for your support,
I have one more doubt, is it possible to put all the calculations in a single measure?
All this calculation is going to be used inside a measure that is going to be shown in the dashboard, it is going to be shown in the return the functions max, min, etc, the reason why it is done this way is because the measure has to be dynamic and the result shown has to vary depending on the selected filters.
I tried to put the measures into one but I got this error
This is the complete measure I am trying to use
The file used is:
https://drive.google.com/file/d/13VxrfEpRmxTaZy-GiVOhwcJD2_2Qqxq7/view?usp=sharing
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
91 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |