Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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:

ALELU_0-1647003186320.png

 

 

7 REPLIES 7
v-yalanwu-msft
Community Support
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.
v-yalanwu-msft
Community Support
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:

 

ALELU_0-1647338286276.png

ALELU_1-1647338313880.png

 

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:

vyalanwumsft_0-1647395459925.png


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

vyalanwumsft_0-1647313103860.png

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:

ALELU_1-1647327815935.png

 

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

Thanks you in advance!

johnt75
Super User
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 )

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors