Missing Stock information data

[p-cantu]

Hi, 

I am getting some data regarding the stock information with the different distributors where I sell some products. Unfortunately, is over 15,000 different products and sometimes, some data of one distributor is missing. I would like to be able to pull the last available data in order not to show a reduction in the stock unless there is really a reduction in stock reported. 

The data looks like this...

Date	Product	Distributor	Stock info
01/05/2023	Brushes	a	10
01/05/2023	Brushes	b	15
01/05/2023	Brushes	c	8
02/05/2023	Brushes	a	10
02/05/2023	Brushes	c	8
03/05/2023	Brushes	a	10
03/05/2023	Brushes	c	8
04/05/2023	Brushes	a	10
04/05/2023	Brushes	b	15
Total	Brushes		94

The final and correct data is: 
 

Date	Product	Distributor	Stock info
01/05/2023	Brushes	a	10
01/05/2023	Brushes	b	15
01/05/2023	Brushes	c	8
02/05/2023	Brushes	a	10
02/05/2023	Brushes	b	15
02/05/2023	Brushes	c	8
03/05/2023	Brushes	a	10
03/05/2023	Brushes	b	15
03/05/2023	Brushes	c	8
04/05/2023	Brushes	a	10
04/05/2023	Brushes	b	15
04/05/2023	Brushes	c	8
Total	Brushes		132

This can help me to identify distributors that require more product because they are running low even before the distributors notice. 

Any help will be appreciated and thank you for the support

[v-rzhou-msft · Accepted solution]

Hi @p-cantu ,

I suggest you to create Dimtables to help calculation.

DimDate = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

DimDistributor = VALUES('Table'[Distributor])

Data model:

Measure:

Measure = 
VAR _CORSSJOIN =
    GENERATE (
        CALCULATETABLE (
            VALUES ( DimDistributor[Distributor] ),
            ALLSELECTED ( DimDistributor )
        ),
        CALCULATETABLE ( VALUES ( DimDate[Date] ), ALLSELECTED ( DimDate ) )
    )
VAR _ADD =
    ADDCOLUMNS ( _CORSSJOIN, "Stock", CALCULATE ( SUM ( 'Table'[Stock info] ) ) )
VAR _ADD1 =
    ADDCOLUMNS (
        _ADD,
        "Filldown",
            VAR _MAXDATE =
                MAXX (
                    FILTER (
                        _ADD,
                        [Distributor] = EARLIER ( [Distributor] )
                            && [Date] <= EARLIER ( [Date] )
                            && [Stock] <> BLANK ()
                    ),
                    [Date]
                )
            VAR _SUM =
                SUMX (
                    FILTER ( _ADD, [Distributor] = EARLIER ( [Distributor] ) && [Date] = _MAXDATE ),
                    [Stock]
                )
            RETURN
                _SUM
    )
RETURN
    IF (
        HASONEVALUE ( DimDistributor[Distributor] ),
        SUMX (
            FILTER (
                _ADD1,
                [Distributor] = MAX ( DimDistributor[Distributor] )
                    && [Date] = MAX ( DimDate[Date] )
            ),
            [Filldown]
        ),
        SUMX ( _ADD1, [Filldown] )
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[p-cantu]

Thank you Rico Zhou