New Enterprise DNA Initiatives

Stock on Hand Total

DAX Calculations
#1

Hi All,

Please find attached a Workbook that I hope you can help me with. I have a Stock on Hand Table which gives a Daily Stock on Hand for each Product and Branch in my Company. There are only records for each Product and Branch where I have either Bought Something or Sold something.

There is a measure called Daily Stock that fills in the Stock on Hand for the days were the Stock on Hand would have been blank but is populated by taking the previous Stock in Hand. All that works ok.

What I cannot seem to figure out is the Total stock on hand for the whole product on a particular day.

The Spreadsheet has the data and the Total I am trying to find should be just over 8000 but I am a little lost.

The issue seems to be the date Context in that it will give me the Total for the actual Transactions but not the filled in gaps.

Hopefully someone can help.

Thanks

Chris

data.csv (19.8 KB) EDNA Stock.pbix (160.8 KB)

#2

Hi @chris.turnbull, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

#3

Hi @chris.turnbull, we’ve noticed that no response has been received from you since the 24th of December. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

#4

Hi @chris.turnbull,

Can you give this a go?
Based on the provided CSV and the active Product filter on 6029931 in your PBIX I guess the total you are looking for is 4063 not something just over 8000… Daily Stock v1 respects context coming from both LocationSK and ProductSK in the table visual.

Daily Stock v1 = 
VAR MaxDate = MAX( 'Calendar'[Date] )
VAR MaxStockDates =
    CALCULATETABLE(
        SUMMARIZE( FactStock, Branch[LocationSK], Products[ProductSK] ),
        'Calendar'[Date] <= MaxDate
    )
RETURN
    SUMX( MaxStockDates, [Daily Stock] )

.
Or since you omitted Branch[LocationSK] in your measure maybe this is what you are after… Daily Stock v2 only respects ProductSK from the table visual and returns that value for each LocationSK

Daily Stock v2 = 
VAR MaxDate = MAX( 'Calendar'[Date] )
VAR MaxStockDates =
    CALCULATETABLE(
        SUMMARIZE( FactStock, Branch[LocationSK], Products[ProductSK] ),
        'Calendar'[Date] <= MaxDate,
        REMOVEFILTERS( Branch )
    )
RETURN

    SUMX( MaxStockDates, [Daily Stock] )

.
With this result

image
image352Ă—585 21.2 KB

Hope this is helpful.

2 Likes
#5

Hi @chris.turnbull, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

#6

Hi @chris.turnbull, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!