Forum Discussion
Advocate IIStock on Hand (Inventory) With Missing Dates for Multiple Sites
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
KellyDid I answer your question? Mark my position as a solution!
Advocate IIHere is a onedrive personal link. I can't share business links outside of my organisation.
Community SupportHi 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.
Kelly
- 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
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
KellyDid I answer your question? Mark my position as a solution!- McGran
Thank you Kelly.
You're an absolute legend. I was beginning to despair about this one.
Cheers
Andrew