Split a Yearly amount in Months according to Start Date and End Date

BENOIT ROLLAND EYROLLES · 2023-11-29T08:02:00+00:00

There was an error rendering this rich post.

BENOIT ROLLAND EYROLLES

Dear All,

'Hope you're fine!

I'm looking for splitting a full year amount between months according to a Start Date and End Date.

For now, I identified 4 cubes: see below.

All cubes have the 3 dimensions: Scenario / Company / Product

Full Year amount (single: at Year level)
Start Date (Date: at Year level)
End Date (Date: at Year level)
Monthly Split (Single: at Month level)

The split must be done in proportion on number of days by months.
From March, 15th 2024 to April, 30th 2024: 46 days splitted in 16 days on March and 30 days in April; thus 460 would be allocated to 160 in March and 300 in April.

Dis you already set this? How? What would be your advices?

Ideally, I'ld rather not use Nexel…

Find more posts tagged with

Dataflow

Procedures

Accepted answers

Fethi ZERARA

Dear @BENOIT ROLLAND EYROLLES ,

More than happy to help ,

Procedure Step 1 : you need to create a Day based Temporary cube ( A ) that contains all the entities on which the calculation must be done. The value 1 must be set every where you are between the Start Date and the End Date.

Procedure Step 2 : You need to put the content of the cube A on a Monthly Based Temporary cube B ( at this step you have the number of active days per Month )

Procedure Step 3 : You need to put the result of A also on a Yearly based cube C ( at this stage you have the number of active days per Year )

Procedure Step 4 : The result is you Monthly Destination cube where you have the DF : Result = Full Year amount*B/C

Best Regards,

Fethi Zerara

Fethi ZERARA

All comments

Tommaso Riva

Hi @BENOIT ROLLAND EYROLLES,

Since Board inherently performs a sum, I recommend performing the calculation on a daily basis.

This approach ensures that when aggregating into a monthly cube, the correct number of days is already assigned.

Ideally the process to calculate the number of days within two dates should be like the following:

where in yellow is the date converted in number.

Admin Task: Create another date cube type, it must be populated for all the days (ideally for the entire time range of the database):

And then convert this date cube to a numeric one (like shown above for start&end date).

In the last column is the number of days in scope.

With this information you can then move on with the monthly allocation.

Hope this helps.

Regards,

Tommaso

Fethi ZERARA

Dear @BENOIT ROLLAND EYROLLES ,

More than happy to help ,

Procedure Step 2 : You need to put the content of the cube A on a Monthly Based Temporary cube B ( at this step you have the number of active days per Month )

Procedure Step 3 : You need to put the result of A also on a Yearly based cube C ( at this stage you have the number of active days per Year )

Procedure Step 4 : The result is you Monthly Destination cube where you have the DF : Result = Full Year amount*B/C

Best Regards,

Fethi Zerara

BENOIT ROLLAND EYROLLES

Dear @Tommaso Riva , @Fethi ZERARA
Thanks for your feedback.
I can't create the new Physical Date cube (@day level) suggested as the application is not at day level; only Month.

I better understand the way to proceed and I'll test on month split at first.

Any other thoughts?

Fethi ZERARA

@BENOIT ROLLAND EYROLLES ,

In this case you can take use of the Function : DATEDIF (start_date, end_date,"d") which gives you the diffirence between to dates.

Example :

Regards,

Fethi Zerara