load data and calculate columns from the summerize table.

[naoyixue1]

Hey there,

I applied the summarize to create a table shown as below.  Also, I created the measure to calculate the inventory as below. 

Right now, I have two problems. 

1: How to calculate the WOS ( use the this month inventory divide by weekly POS in next month, based on how many weeks we have in each month ) (see example below).  Do you have some hints or thoughts how I should calculate that? 

Jan WOS = Jan Inv / (Feb POS / 4 weeks)

Feb WOS = Feb Inv / (Mar POS / 5 weeks) 

2:  In the original one, I calculated the inventory shown as before from Jan thru Dec. Well, since we will actualize month ending inventory when we finish each month. Givne that, i just wonder how I can set up the right inventory in the table ( actualized one + the calculated one)

1: Should I use the summarize to also include the actualized month inventory in this table. ( it means only some months have the value with the actualized value, and the others with blank)

2: How can I calculate from one point of ending inventory. For example, I already have actualized inventory to the end of Apr. Given that, how I can calculate the remaining one (from May thru Dec)? Since I use summarize to create the table with Month, POS and Receiving. 

d 

Really appreciate yall helps!

[naoyixue1]

@Anonymous Thanks Gao! Do you have some recommendations how I should do in query? Thanks. Sorry, I'm in my iternational business trip, and try your way and let you know the outcome. Thanks!

[Anonymous]

Hi @naoyixue1 ,

Please new a column like:

WOS = 
VAR _month = 'Test Table A'[Month]
VAR _next_month = _month + 1
VAR _next_pos = CALCULATE(MAX('Test Table A'[POS1]),FILTER(ALL('Test Table A'),'Test Table A'[Month]=_next_month))
VAR _week = CALCULATE(MAX('Calendar'[Week in Month]),FILTER(ALL('Calendar'),'Calendar'[Month Number]=_next_month))
VAR _result = DIVIDE('Test Table A'[Inventory1],DIVIDE(_next_pos,_week))
RETURN
_result

2. DAX is not good at dealing with recursion problems. It is recommended to handle this in PowerQuery.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data