SUM when complete the quantity in other CSV file

[JuanVR11]

Hello friends of the group. I have an issue that I honestly don't know how to solve in DAX and I hope you can guide me.
I have 2 CSV files in a Power BI model.
in first place a CSV called STOCK contains a material code and their respective stock quantity.
In the other a CSV called MOVEMENTS that contains the same materials code related to a movement date and the amount reduced on that date.

What i need is:

Add 90 days to the "Fecha de Entrada" but only until the sum of "Ctd.en UM entrada" is equal to the amount of stock in the CSV STOCK for
the respective material in CSV STOCK and its "ALMACÉN" is "". The sum must be from the most recent date to the oldest of the 
CSV MOVEMENTS.
For example, in the image two Material appear with their respective qty in Stock, i have to add the Ctd.en UM entrada 
marked in green ticket until the amount of the sum will be equal to the stock and add +90 days to the "Fecha de entrada" date.

I hope you can help me, since I don't see how to solve this.

Thank you!

        

[JuanVR11]

Somebody can help me please?

[v-xicai]

Hi @JuanVR11 ,

Based on created relationship between table MOVEMENTS and STOCK, you may create columns in table Material like DAX below.

Group rank = RANKX(FILTER(MOVEMENTS, MOVEMENTS[Material]=EARLIER(MOVEMENTS[Material])),MOVEMENTS[Fecha de Entrada],,DESC ,Skip)
​
Cumulative sum = CALCULATE(SUM(MOVEMENTS[Ctd.en UM entrada]),FILTER(MOVEMENTS, MOVEMENTS[Material]= EARLIER(MOVEMENTS[Material])&&MOVEMENTS[ALMACÉN]=BLANK()&&MOVEMENTS[Group rank] < EARLIER(MOVEMENTS[Group rank])))
 
Add 90 days=IF(MOVEMENTS[Cumulative sum ]=RELATED(STOCK[Stock]), MOVEMENTS[Fecha de Entrada]+90, BLANK() )

If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JuanVR11]

Hi V-xicai.

Thanks a lot for your answer.

Attached is the pbix file with the sample of what i have, and in the image the expect result in a third table.

https://1drv.ms/u/s!AmfHhSleU-H8jV4mJXqAkUpCZ784?e=Srwe4z

i hope u can help me with this problem.

Thanks!

[v-xicai]

Hi @JuanVR11 ,

If you need to get "Fecha de entrada +30", you can create column like DAX below.

Add 30 days =[Fecha de entrada]+30

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JuanVR11]

Hi v-xicai.

Thanks for your answer.

I know that i have to add a column to add 30 days to date, but my problem occurs when i try to paste the DAX sentence in your first answer.

For some reason i have problems in the EARLIER sentence.

I upload a PBIX example with the model in OneDrive. could u help me doing that there?

Thanks a lot!

Best Regards

Juan