Missing Stock information data

Question

Hi,&nbsp;&nbsp;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.&nbsp;&nbsp;The data looks like this...DateProduct&nbsp;DistributorStock info01/05/2023Brushesa1001/05/2023Brushesb1501/05/2023Brushesc802/05/2023Brushesa1002/05/2023Brushesc803/05/2023Brushesa1003/05/2023Brushesc804/05/2023Brushesa1004/05/2023Brushesb15TotalBrushes&nbsp;94&nbsp;The final and correct data is:&nbsp;&nbsp;DateProduct&nbsp;DistributorStock info01/05/2023Brushesa1001/05/2023Brushesb1501/05/2023Brushesc802/05/2023Brushesa1002/05/2023Brushesb1502/05/2023Brushesc803/05/2023Brushesa1003/05/2023Brushesb1503/05/2023Brushesc804/05/2023Brushesa1004/05/2023Brushesb1504/05/2023Brushesc8TotalBrushes&nbsp;132This can help me to identify distributors that require more product because they are running low even before the distributors notice.&nbsp;Any help will be appreciated and thank you for the support

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
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] )
                            &amp;&amp; [Date] &lt;= EARLIER ( [Date] )
                            &amp;&amp; [Stock] &lt;&gt; BLANK ()
                    ),
                    [Date]
                )
            VAR _SUM =
                SUMX (
                    FILTER ( _ADD, [Distributor] = EARLIER ( [Distributor] ) &amp;&amp; [Date] = _MAXDATE ),
                    [Stock]
                )
            RETURN
                _SUM
    )
RETURN
    IF (
        HASONEVALUE ( DimDistributor[Distributor] ),
        SUMX (
            FILTER (
                _ADD1,
                [Distributor] = MAX ( DimDistributor[Distributor] )
                    &amp;&amp; [Date] = MAX ( DimDate[Date] )
            ),
            [Filldown]
        ),
        SUMX ( _ADD1, [Filldown] )
    )
Result is as below.

&nbsp;
Best Regards,Rico Zhou
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

Forum Discussion

Missing Stock information data

2 Replies

Related content

Missing information

Inventory and Stock balance calculation

stock ageing

Pulling Historic Stock Market Information

missing data

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