Stock in and out

[Anonymous]

Hi all,

Another begginer question. I am working with products entering and leaving a facility, and I am trying to get the count of stock at any particular date.

I have a stock dimension with product ID, category, date of entry and the date of leaving, as such:

And I want to know the total count of each category at any given time. This is what the desired outcome would be (I've entered this manually):

I have already created a calendar table, so I only need to add the two columns with counts for A and B.

Hope it makes sense, and as always.

many thanks!

[v-chuncz-msft]

@Anonymous ,

You may refer to the following posts.

• https://community.powerbi.com/t5/Desktop/Trend-from-History-Table/m-p/200304#M88115
• https://community.powerbi.com/t5/Desktop/Create-date-Parameter-asat-date-to-get-active-customers/m-p/311306#M137927

[Anonymous]

@v-chuncz-msft ,

Thanks for your reply. Unfortunately that doesn't seem to work for me.

I am no expert in DAX, so maybe I am inputing the code incorrectly, however, even that measure would not provide me with the solution I need, which is a different count for each category, not just a total count.

Would really appreciate some help 🙂

[Anonymous]

Any new ideas?

[Anonymous · Accepted solution]

I've found the solution, I leave the code I used for future reference:

Count A = CALCULATE(COUNTROWS('in and out'), FILTER('in and out','in and out'[Category]="A"),filter(ALL('in and out'),'in and out'[Date In] <= 'Calendar'[Date])) - CALCULATE(COUNTROWS('in and out'), FILTER('in and out','in and out'[Category]="A"),filter(all('in and out'),'in and out'[Date out] <= 'Calendar'[Date])) + CALCULATE(COUNTROWS('in and out'),FILTER('in and out','in and out'[Category]="A"), FILTER('in and out','in and out'[Active]))

[Anonymous]

Any ideas?