Consumption of stock

[Anonymous]

Hello Dear Gurus,

I have faced to a task, I need to calculate rolinfg stock, 

(PP+CV14) - (Stock+ PO), the main problem that all calculations have on date:

[v-yalanwu-msft]

Hi, @Anonymous

Is your problem solved? If so, Would you mind accept the helpful replies as solutions? Then we could close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @Anonymous ;

can you share  a simple data removing sensitive information, along with the results you want to output?

[Anonymous]

Unfortunately  I can't, but I checked you solution one more time,  it's almost work for me except main calculation, I will try to explain my idea in Excel version:

[v-yalanwu-msft]

Hi, @Anonymous ;

Try it.

Rolling Stock = 
var _Date = MAX('Table'[Date] )
var _mindate=MINX(ALL('Table'),[Date])
return 
IF(_Date=_mindate,MAX('Table'[Stock]),
MAX('Table'[Stock])-CALCULATE( SUMX( 'Table', [PP] +[CV14]-[PO] ),FILTER(ALL('Table'),[Date]<=_Date&&[Date]<>_mindate)))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @Anonymous ;

Also could try .

Rolling Stock = 
var maxDate = MAX('Table'[Date] )
return CALCULATE( SUMX( 'Table' , 'Table'[PP] + 'Table'[CV14] - 'Table'[PO]  ),FILTER(ALL('Table'),[Date]<=maxDate))-MAX('Table'[Stock])

The final output is shown below:

Or can you share the results you want to output?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello Yalan,

I think  I need to make some clarifications about my data set, because it's not a table:

Unfortunately the way which you advised me didn't help me or I do something wrong.

Thanks you in advance!

[johnt75]

Try

Rolling Stock Total =
var maxDate = MAX( 'Date'[Date] )
return CALCULATE( SUMX( 'Table' , 'Table'[PP] + 'Table'[CV14] - ( 'Table'[Stock] + 'Table'[PO] ) ),
REMOVEFILTERS( 'Date'[Date]), 'Date'[Date] <= maxDate )