Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have a dax calculatioin of
Solved! Go to Solution.
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.
Proud to be a Super User!
could you pls provide the sample data and expected output?
Proud to be a Super User!
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
could you pls create some dummy data and provide the expected output?
Proud to be a Super User!
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
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.
Proud to be a Super User!
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 !!
you are welcome
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
107 | |
88 | |
76 | |
67 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |