Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Yeztrom
Helper I
Helper I

Calculate number of consecutive days in stockout

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.

 

Yeztrom_1-1654277385581.png

 

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

 

 

 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1655272890563.png

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.

View solution in original post

4 REPLIES 4
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1655272890563.png

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.

sturlaws
Resident Rockstar
Resident Rockstar

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

Yeztrom_0-1654626754986.png

The measure is:

stock =
VAR _PRESENCE_BY_PRODUCT =
SUMMARIZE(
            vw_product_availabilies,
            vw_product_availabilies[master_product_id],
            vw_product_availabilies[retailer_id],
            "Presence",[Avg. Presence]
)
VAR _FILTER_PRODCUTS_W_PRESENCE =
SUMMARIZE(
            ADDCOLUMNS(
                        SUMMARIZE(
                                    FILTER(
                                            _PRESENCE_BY_PRODUCT,
                                            [Presence] = 1
                                    ),
                                    [retailer_id],
                                    [master_product_id]
                        ),
                        "RET-PROD",[retailer_id]&"-"&[master_product_id]
            ),
            [RET-PROD]
)
VAR _FILTER_AVAILABILITES_TABLE =  
FILTER(
        ADDCOLUMNS(
                    VALUES(vw_product_availabilies),
                    "RET-PROD",[retailer_id]&"-"&[master_product_id]
                ),
        [RET-PROD] IN _FILTER_PRODCUTS_W_PRESENCE
)

VAR _CONSOLIDATE_STORE_ID =
ADDCOLUMNS(
            GROUPBY(
                        _FILTER_AVAILABILITES_TABLE,
                        [retailer_id],
                        [master_date_id],
                        [master_product_id],
                        "Available", SUMX(CURRENTGROUP(),[product_available])
            ),
            "Date",Date(LEFT([master_date_id],4),MID([master_date_id],5,2),RIGHT([master_date_id],2))
        )

VAR _t1 =
    CALCULATE (
        MAXX ( _CONSOLIDATE_STORE_ID,[Date] ),
        FILTER (
            ALL ( _CONSOLIDATE_STORE_ID ),
            [retailer_id] = EARLIER ( [retailer_id] )
                && [master_product_id] = EARLIER ( [master_product_id] )
                && [Available] <> EARLIER ( [Available] )
                && [Date] < EARLIER ( [Date] )
        )
    )
RETURN
_t1
 
The file used 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

Yeztrom_0-1654624555768.png

 

This is the complete measure I am trying to use

stock =
VAR _PRESENCE_BY_PRODUCT =
SUMMARIZE(
            vw_product_availabilies,
            vw_product_availabilies[master_product_id],
            vw_product_availabilies[retailer_id],
            "Presence",[Avg. Presence]
)
VAR _FILTER_PRODCUTS_W_PRESENCE =
SUMMARIZE(
            ADDCOLUMNS(
                        SUMMARIZE(
                                    FILTER(
                                            _PRESENCE_BY_PRODUCT,
                                            [Presence] = 1
                                    ),
                                    [retailer_id],
                                    [master_product_id]
                        ),
                        "RET-PROD",[retailer_id]&"-"&[master_product_id]
            ),
            [RET-PROD]
)
VAR _FILTER_AVAILABILITES_TABLE =  
FILTER(
        ADDCOLUMNS(
                    VALUES(vw_product_availabilies),
                    "RET-PROD",[retailer_id]&"-"&[master_product_id]
                ),
        [RET-PROD] IN _FILTER_PRODCUTS_W_PRESENCE
)

VAR _CONSOLIDATE_STORE_ID =
ADDCOLUMNS(
            GROUPBY(
                        _FILTER_AVAILABILITES_TABLE,
                        [retailer_id],
                        [master_date_id],
                        [master_product_id],
                        "Available", SUMX(CURRENTGROUP(),[product_available])
            ),
            "Date",Date(LEFT([master_date_id],4),MID([master_date_id],5,2),RIGHT([master_date_id],2))
        )

VAR _t1 =
    CALCULATE (
        MAXX ( _CONSOLIDATE_STORE_ID,[Date] ),
        FILTER (
            ALL ( _CONSOLIDATE_STORE_ID ),
            [retailer_id] = EARLIER ( [retailer_id] )
                && [master_product_id] = EARLIER ( [master_product_id] )
                && [Available] <> EARLIER ( [Available] )
                && [Date] < EARLIER ( [Date] )
        )
    )
RETURN
_t1

 

The file used is: 

https://drive.google.com/file/d/13VxrfEpRmxTaZy-GiVOhwcJD2_2Qqxq7/view?usp=sharing

 

 

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.