Forum Discussion

Mukombo's avatar
Mukombo
Regular Visitor
5 years ago
Solved

Stock at Hand from running total

Hello team,

I have 2 tables. 1 table has stock in dates when the stock was purchased. Another table has daily sales. On any given day, from the available stock on that date, I would like to know what is the balance. I am using a timeline visual. What is happening is when I choose a date from the timeline, it is giving me a balance as though nothing has been consumed in the past. This data is only for a few months and I cannot calculate the SAMEPERIODLASTYEAR. Attached is the PBIX file, Please help.

Thanks

 

https://drive.google.com/file/d/1sYbY0cKN1qSTpshkcn7XgkabW5r9j2Se/view?usp=sharing

 

PBIX File 

Need Help
  • Mukombo's avatar
    Mukombo
    5 years ago

    Dear aj1973,

    We are almost there. The first screenshot is exactly how I want it. But you notice in the second screenshot when you select April, it is adding stocks for August too, which should not be the case. When I choose a month that is before August, I should only see stocks that were available within that period. And when I extend the period, it should look at what was available. When i select August, I need to add all stock received but should subtract the sales. Hope I am clearer

6 Replies

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Mukombo 

    Can you give me a screenshot on what is wrong and how you want the result to look like?

    Also to use SAMEPERIODLASTYEAR you need relate the 2 tables

     

    However in your sales Table you don't have data from year 2020, how do you expect the formula to give you a result from the same period last year?

    • Mukombo's avatar
      Mukombo
      Regular Visitor
      5 years ago

      Hi aj1973

       

      I do not need THISSAMEPERIODLASTYEAR, Instead, I just want to look at what do i have available currently based on the selected date if I subtract what I have sold since the first day. Considering the dates when the stock was brought in. In the screenshot, when i choose April, the current balance is adding up the future stock as well, which shouldn't be the case, it should only look at the current stock on hand and less the previous sales stock. Likewise, when I choose the future date, it should add all stocks but subtract sales to that selected date.

      Thanks