Subtracting stock by date and product

[thatjoey20]

Hello everybody, 

I have a problem that I trying to solve using DAX code, that is the following case:

In my table I have, Date, Stock, Product and Quantity. I need a measure that subtracts the stock  by the quantity, and be cummulative, for example:

This needs to be ordered by date and per product, so only first date 20/03 will have stock to sell having 21 remaining. 

[Ashish_Mathur · Accepted solution]

Hi,

Ensure there is a Calendar Table.  Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calnedar Table.  To your visual, drag Date from the Calendar Table.  Assuming you have already written measures of Stock and Quantity, write these measures:

Cumulative quantity = calculate([quantity],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

Diff = [Stock]-[Cumulative Quantity]

The Cumulative quantity measure will calculate the quantity from the very inception.

Hope this helps.

[v-xinruzhu-msft · Accepted solution]

Hi @thatjoey20 

The solution @Ashish_Mathur  provided is excellent, and you can also refer to the following solution.

Sample data

Create a measure.

MEASURE =
VAR a =
    CALCULATE (
        SUM ( 'Table'[Quantity] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Date] <= MAX ( 'Table'[Date] ),
        'Table'[Product Code] IN VALUES ( 'Table'[Product Code] )
    )
RETURN
    MAX ( 'Table'[Stock] ) - a

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Thank you @v-xinruzhu-msft 

[Ashish_Mathur · Accepted solution]

Hi,

Ensure there is a Calendar Table.  Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calnedar Table.  To your visual, drag Date from the Calendar Table.  Assuming you have already written measures of Stock and Quantity, write these measures:

Cumulative quantity = calculate([quantity],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

Diff = [Stock]-[Cumulative Quantity]

The Cumulative quantity measure will calculate the quantity from the very inception.

Hope this helps.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...