Cross model copy from onthly granularity to daily granularity

[Freddie]

Hello,

I have made a cross model copy of gross revenue from a model using monthly granularity to a model using daily granularity. Now all the gross revenue for each month is posted to the first day in the month each respective month.

My question is: how do I make an advanced formula in a data action to distribute these values equally on each day in the respective month?

Best, Frederik

[N1kh1l]

frederik96

You did not share your model dimensionality but assuming a simple model with Account, Measures,Version and date the below should do the job for you. I have also assumed that revenue received on first day of month will be replaced by equal portion of the revenue per day. So if you receive 3100 in January 1 2023, After allocation January 1 will have 100. I mean data intersection pre allocation and post allocation is same unless you have different audit trail or version to differentiate.

Pre Allocation revenue received on 1st Day of each month. For example I have taken Jan, Feb and March

Data Action Code: Adjust as per your model and dimensions.

MEMBERSET [d/Measures]="Amount"
MEMBERSET [d/Account]="Revenue"

MEMBERSET [d/Date]=BASEMEMBER([d/Date] , "[2023]")

VARIABLEMEMBER #REV OF [d/Account]
INTEGER @I
DATA([d/Account]=#REV)=RESULTLOOKUP()

FOREACH [d/Date]
	@I=DAY([d/Date])-1
	DATA()=RESULTLOOKUP([d/Account]=#REV,[d/Date]=PREVIOUS(@I))/DAYSINMONTH([d/Date])
ENDFOR

Post Allocation: I am not showing all days in snapshot below but all days have received equal amounts

January

February

March

I would still advise that you seperate the pre allocated and post allocated data using some dimension like audittrail or Account so that you can preserve the original revenue received and run the allocation multiple times. If you do not do that, next time you run this the post allocated amount ( jan 100, feb 200, mar 300) on 1st of each month will be allocated again equally.

Hope this helps !!

Please upvote/accept if this helps

Nikhil