Distribute cost over months based on contracts start and end date.

Question

I need help in OAC. Solution through calculation workbook or Dataflow will do.

I have contracts details like below.

Contract

Start date

End date

Amount

ABC

4/15/2025

4/14/2026

24000

DEF

1/1/2024

6/30/2025

16000

GHI

8/1/2025

7/31/2025

12000

I want to split the data for monthly and show the month spending. I want data like below.

Months

Contract

Start date

End date

Amount

Jan-25

Feb-25

Mar-25

Apr-25

May-25

Jun-25

Jul-25

Aug-25

Sep-25

Oct-25

Nov-25

Dec-25

Total

ABC

4/15/2025

4/14/2026

24000

2000

18000

DEF

1/1/2024

6/30/2025

16000

1000

5000

GHI

8/1/2025

7/31/2026

12000

1000

5000

Total

52000

1000

3000

2000

3000

28000

Rajakumar Burra · Answer

Thanks @Gianni Ceresa  Will try and let you know.

Gianni Ceresa · Answer

There is nothing in the product "generating" rows out of nowhere, therefore you need a query to generate those rows for you.

If you have your "contract details" in a table, and you have a time dimension in a different table, by joining them together, you can generate the rows you need.

Just to give you an idea of what I mean:

SELECT …

FROM "contract details" c, "time dimension" t

WHERE t.date BETWEEN c."start date" AND c."end date"

This will generate all the rows representing the various months of existence for a project. Of course you need to add the logic to handle the month level (depends on your time dimension), the logic you want on when it should finish (before the month of the "end date") etc.

If you can use full SQL with an Oracle database you can skip the "time dimension" part and make that one up with a select level from dual connect by level < 100 and then turn this into a time dimension with one month per row.

Depending on what your data sources are, you can do that directly in a query, or using a data flow if needed. Just in a workbook you will probably struggle because the modelling is too basic I would say (while the semantic model could handle that easily).

Rajakumar Burra · Answer

@Gianni Ceresa There is a typo in GHI contract end date. I have corrected it.

I can easily create calculation for monthly amount. Logic is simple and easy.

My business logic is how much we are spending in each month on all contracts. Contract period varying from 1 month to 5 years.

Appreciated if you provide some syntax or example on  your comments.

Overall, if you do join your "contract details" with a full time dimension using the start date and end date as join condition on the month (applying the required business logic you need as apparently you want to stop at the month before to end date)