Stock on Hand (Inventory) With Missing Dates for Multiple Sites

[McGran]

I have a stock file that uses [DATE FROM] and [DATE TO] rather than daily snapshots to track the stock position.  I want to report the daily stock position, filling missing dates with the last known position.

This code does exactly what I want in the column, but I'd like the row to also show the sum of stock across sites.

STOCK ON HAND =
CALCULATE (
    SUM ( 'ODS STOCK_LINECODE_DC'[AVAILABLE_STOCK] ),
    LASTNONBLANK (
        FILTER (
            ALLSELECTED ( 'ODS FINANCIAL_CALENDAR'[FINANCIAL_DATE] ),
            'ODS FINANCIAL_CALENDAR'[FINANCIAL_DATE]
                <= MAX ( 'ODS FINANCIAL_CALENDAR'[FINANCIAL_DATE] )
        ),
        CALCULATE ( SUM ( 'ODS STOCK_LINECODE_DC'[AVAILABLE_STOCK] ) )
    )
)

Without measure - Missing dates are returned as blanks and column totals sum stock

	Loc1	Loc2	Total
June	636	4	640
2	321	0	321
3		1	1
4	315	2	317
5		1	1

With Current Measure - Missing dates are now correctly populated and column total shows closing stock position for the period.  However the row totals are not accounting for the newly populated data.

	Loc1	Loc2	Total
June	315	1	1
2	321	0	321
3	321	1	1
4	315	2	317
5	315	1	1

Ideal Measure - Missing dates are still correctly populated and column total shows closing stock position for the period.  Row totals are account for the newly populated data.

	Loc1	Loc2	Total
June	315	1	316
2	321	0	321
3	321	1	322
4	315	2	317
5	315	1	316

Is this possible?

[mahoneypat]

Please try this expression, substituting your actual table and column names

New Measure = SUMX(VALUES(Table[Location), [Stock On Hand])

Where Location is the column with Loc1, Loc2, and Stock on Hand is your "Current Measure" that give correct values in the columns (but not the row total).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[McGran]

Thank you for the reply.

I can understand what you're trying to do here (and got my hopes up) but, unfortunately, this too results in the blanks reappearing as per table 1.

[v-kelly-msft]

Hi @McGran ，

It's a grandtotal issue ,very common issue. Could you pls upload your .pbxi file to onedrive business and share the link with us?

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[McGran]

Here is a onedrive personal link.  I can't share business links outside of my organisation.

https://1drv.ms/u/s!AtfEymMoKET_h2uZ9CYBNdBmoZca?e=aQP5u0 

[v-kelly-msft]

Hi  @McGran ,

Create a measure as below:

_Stock on hand = var _maxdate=CALCULATE(MAX('ODS STOCK_LINECODE_DC'[VALID_FROM_DATE]),FILTER(ALL('ODS FINANCIAL_CALENDAR'),'ODS FINANCIAL_CALENDAR'[FINANCIAL_DATE]<MAX('ODS FINANCIAL_CALENDAR'[FINANCIAL_DATE]))) 
return
var _sum=CALCULATE(SUM('ODS STOCK_LINECODE_DC'[AVAILABLE_STOCK])) return
IF(ISBLANK(_sum)&&NOT(ISBLANK(_maxdate)),CALCULATE(SUM('ODS STOCK_LINECODE_DC'[AVAILABLE_STOCK]),FILTER(ALL('ODS FINANCIAL_CALENDAR'),'ODS FINANCIAL_CALENDAR'[FINANCIAL_DATE]=_maxdate)),_sum)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[McGran]

Hi Kelly, thanks for your reply.

The desired result is to keep the column total results as is but the row total results should sum for all locations.

So the Qtr and June column totals are correct, both are 20, which is the closing stock position for the month and quarter.  However the row total for these is showing 0 when it should show 20.

3rd June, the row total is 1 but should be 322.  We can see that this correlates with the missing data on the WITHOUT MEASURE report.

[v-kelly-msft · Accepted solution]

Hello @McGran ,

Create a dim table as shown below:

Dim = VALUES('ODS STOCK_LINECODE_DC'[LOCATION_KEY])

Next, create a relationship as shown below:

Finally, create a measure as follows:

Measure = SUMX(VALUES(Dim[LOCATION_KEY]),[STOCK ON HAND])

And you'll see:

For the related .pbix file, see pls see attachment.

Saludos
Kelly

Did I answer your question? Mark my position as a solution!

[Anonymous]

Hi V Kelly,

Thanks for this is it was my exact issue with my model being the same but I want my storess on rows and I want a total for all Stores on a particular day and I do not seem to beable to compute that.  Can you point me in the right direction thanks Chris.

[McGran]

Thank you Kelly.

You're an absolute legend.  I was beginning to despair about this one.

Cheers

Andrew

[amitchandak]

@McGran , Try m1 or m2

m1= lastnonblankvalue(Table[Date],max(Table[Total])

or

m1= lastnonblankvalue(Table[Date],sum(Table[Total])

m2= calculate([m1], allexcept(Table, Table[month],Table[location])) //it uses m1

[McGran]

Thanks for your reply.

Both versions m1 don't remove the blanks so I get a result like the first example table.

m2 doesn't remove the blanks and returns 0 for all values