Stock at Hand from running total

[Mukombo]

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 

[aj1973]

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]

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

[aj1973]

Hi @Mukombo 

Is this how you want to see it!

[aj1973]

Hi @Mukombo 

Sorry but where do you see August when I select April!?

Here is the file, check it out and let me know

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

[Mukombo]

Thanks a lot!

This is very helpful. Let me work with this. If I need further support, I will revert back

[Mukombo · Accepted solution]

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