Power BI Desktop creating a separate DAX calculation for certain rows

[Nathan__Mox]

Hi all, 

I have a dax calculatioin of 

Column = [on Hand] + sumx(filter('graph test', 'graph test'[date] <= earlier('graph test'[date]) && [Item code] = earlier([item code]) ), [Qty] - [Weekly max Sales] ) , which calculates current/forecast stock level. Ive now realised that The current week is included in this calculation but the on hand value has already been calculated for the current week, so the current week should just equal on hand. If someone can help me exclude the 1st row of each different Category from the above DAX calculation and make it just equal on hand I would be very thankful.

If any more context or tables are needed let me know.

Thanks

[ryan_mayu]

@Nathan__Mox 

could you pls provide the sample data and expected output?

[Nathan__Mox]

Below is my data (sorry for the mess had to cover sensitive infomation), week 28 (3rd row from bottom) is the current week. Since the on hand column has already subtracted the sales and added the inbound items for the current week I dont want it done twice as seen below (I know on hand is 455 and the inbound items is 2500 for current week thats just mock data as at the moment im waiting for Inbound items data). Instead I want the current week to equal on hand without messing up current calculation. On top of that the current week row needs to be automated so that next week, week 29 will be current week and will equal on hand value.

If you could please help me out Ill be very grateful.

If any other infomation is needed let me know

Thanks

[ryan_mayu]

@Nathan__Mox 

could you pls create some dummy data and provide the expected output?

[Nathan__Mox]

Current

Week number.	Date (Week starting).	Item Code.	Qty (inbound Items).	Weekly max sales.	forecasted stock levels	on hand
28	4th july	a	0	25	15	40
29	11th july	a	50	25	40	40
30	18th july	a	0	25	15	40
31	25 july	a	20	25	10	40
28	4th july	b	40	35	55	50
29	11 july	b	50	35	70	50
30	18th july	b	0	35	35	50
31	25th july	b	0	35	0	50

What I want

Week number.	Date (Week starting).	Item Code.	Qty (inbound Items).	Weekly max sales.	forecasted stock levels	on hand
28	4th july	a	0	25	(<-- ignore) 40            (= -->)	40
29	11th july	a	50	25	65	40
30	18th july	a	0	25	40	40
31	25 july	a	20	25	35	40
28	4th july	b	40	35	(<-- ignore) 50               (= -->)	50
29	11 july	b	50	35	65	50
30	18th july	b	0	35	30	50
31	25th july	b	0	35	-5	50

Thanks

[ryan_mayu · Accepted solution]

@Nathan__Mox 

pls try this

Column = if('Table'[Date (Week starting).]=CALCULATE(min('Table'[Date (Week starting).]),ALLEXCEPT('Table','Table'[Item Code.])),'Table'[on hand], 99999)

replace 99999 with your current formula.

[Nathan__Mox]

Thank you heaps.

instead of having the 99999 at the end of calculation I swapped it for this calc [on Hand] + sumx(filter('graph test', 'graph test'[date] <= earlier('graph test'[date]) && [Item code] = earlier([item code]) ), [Qty] - [Weekly max Sales] ) which worked perfectly.

Thank you !!

[ryan_mayu]

you are welcome