Distribute an amount between two dates

[nuriac]

Hello

I would like to know which formula I must use in order to distribute the amount (of an order) between two dates, the date of start and the end date of the service.

The idea is that having an amount and the start and end date, because you know what amount I will bill each month.

Example:

Order 1: 100,000 euros; start 1/11/2020 and end 1/11/2021

although I've seen some post with similar information, I haven't been able to apply it.

Thank you

[amitchandak · Accepted solution]

@nuriac , Check if the attachment can help you.

[nuriac]

It puts the correct values to me, but they only come out in one month, for example if an order lasts from January to May, I only get values for that order in the month of January. The January value is correct, but I lack data in the other months.

Value by day of Month ? CALCULATE(SUMX(SUMMARIZE(filter(CROSSJOIN(CALENDAR,'SELL ORDERS'),CALENDAR[Date] >'SELL ORDERS'[Date Shipping]&& CALENDAR[Date]<' 'SALES ORDERS'[End Date]),'SELL ORDERS'[Sales Order No],CALENDAR[Date],'SALES ORDERS'[[Measures]].[ Expected Billing]]],'SALES ORDERS'[Shipping Date],'SALES ORDERS'[End Date]),DIVIDE('SALES ORDERS'[[Measures]].[ Expected Billing]]],DATEDIFF('SALES ORDERS'[Shipping Date],'SALES ORDERS'[End Date],DAY)+1)))

[nuriac]

Solved.

It had related the calendar to the start date and therefore did not distribute correctly between the months. Removing the relationship has been perfect.

Thank you