Sales - stock Calculation

[Iamnvt]

hi,

I have a Sales table:

Order      Date               Product  Sales

1	11/25/2017	A	10
2	11/26/2017	B	20
3	11/27/2017	A	5

and a Stock table:

Product    Quantity

A	1
B	2

How can I make a DAX measure to calculate the Material Requirement for each date?

Eg. 

Date                Product   Requirement

11/25/2017	A	9
11/26/2017	B	18
11/27/2017	A	5

the tricky part is that the stock table does not have date column, meanwhile the Sales table has the Date column.

sample file is here:

https://1drv.ms/x/s!Aps8poidQa5zkvFwWm3YbzFb15bkTw

thanks

[DAX0110]

Hi @Iamnvt, if you begin by creating a measure that sums the total product sales for each product before the current date, then you will quickly arrive at a simple formula for Material Requirement.

[Iamnvt]

Hi,

It is not working when you put the date field into the pivot.

Br

[DAX0110]

Hi @Iamnvt, can you upload the sample file elsewhere?  I can't download it from the current host.

[Iamnvt]

it is Onedrive link. My friend can download it well. Could you please try again? @DAX0110

[DAX0110 · Accepted solution]

Hi @Iamnvt, I did some work on your sample workbook - please see:

Modified workbook

[Iamnvt]

hi,

thanks for the answer. However, it doesn't give the correct result yet.

Order        Date             Product    Requirement

1	11/25/2017	A	9
2	11/26/2017	B	18
3	11/27/2017	A	5

I understand your approach of running total, and able to modify the DAX to give the correct result.

Questions regarding to the DAX:

1. What is the purpose of calling the variants with firstnonblank?

2. when I remove the date field out of the pivot. it seems not working. How can I fix that?

ProductPBI           Total Sold Qty       PBI Running Sold Qty      PBI Stock Qty     PBI Material Requirement

A	15	0	1
B	20	0	2

[DAX0110]

Hi @Iamnvt,

Good to see that you can debug my DAX formula - it's always harder to troubleshoot someone else's work than starting over from scratch yourself, so good work!

As for FIRSTNONBLANK - you can also use SELECTEDVALUE if doing this in Power BI Desktop.  It has to be used because there is only a filter context at that point. Try remove it and see what happens.

The pivot table after removing date?  Well it's still working.  This is my screenshot:

[Iamnvt]

I think I found the correct debugged version now. It works even if I removed the date field out of the pivot.

=
VAR runningQty = [PBI Running Sold Qty]
VAR thisQty = [PBI Total Sold Qty]
VAR stockQty = [PBI Stock Qty]
VAR reqmt = IF( stockQty > runningQty
 , IF(thisQty + runningQty > stockQty
 , thisQty+runningQty - stockQty
 ,BLANK())
 ,thisQty
 )
RETURN IF( ISBLANK( thisQty ), BLANK(), reqmt )

 Still few questions:

1. About the Running total formula, why do you use the VAR version to calculate that instead of the common cumulative pattern?  

=CALCULATE(Sales[PBI Total Sold Qty], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date])))

 I tried to replace  [PBI  Running Sold Qty] with the formula above. It gives the correct result, but when moving the Date field out, it is not correct anymore.

What is the correct Running total formula with FILTER if I don't use the VAR version?

2. Is there any other elegant solution? I think it is quite a simple problem; however, the solution seems not easy.