cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Consumption of stock

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:

7 REPLIES 7
Community Support

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.
Community Support

Hi, @Anonymous ;

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

Anonymous
Not applicable

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:

Community Support

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.

Community Support

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
Not applicable

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!

Super User

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 )`