Forum Discussion

nuriac's avatar
nuriac
Icon for Helper III rankHelper III
5 years ago
Solved

Distribute an amount between two dates

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 a...
nuriac's avatar
nuriac
Icon for Helper III rankHelper III
5 years ago

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's avatar
    nuriac
    Icon for Helper III rankHelper III
    5 years ago

    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