Add data to different fields according to StartDate and Duration in Months and Year

[Anonymous]

Hi,

I am making an application, in which employees can add data to a give project. The data gets stored in our "MainTable" of an excel spreadsheet. The fields include "CurrentBudget", StartDate (datefinder), DurationInDays and EndDate (calculated by datediff of start + durationindays).

With this information each field of "Jan2020","Feb2020"........."Dec2020","Jan2021",......"Dec2021" get updated so that it divides the "CurrentBudget" according to the month the project spans over and how many month.

It looks like this in the editform:

Each monthfields datacard has the following Default property:

If(2020=Year(EstimatedStart.SelectedDate)&&1>=Month(EstimatedStart.SelectedDate)&&1<=Month(DateAdd(EstimatedStart.SelectedDate,Value(DurationDaysDataCardValue.Text),Days)),

(If(
    (And(Value(DurationDaysDataCardValue.Text)<=30,!(Month(EstimatedStart.SelectedDate)=Month(EstimatedFinish.SelectedDate)))) ,(Value(CurrentBudgetDataCard.Text)/2),

So the above is for January, if February it would have 2 for month in the first line of the formula, etc. etc. according to month.

However:

The problem arises when a project spans over one year to another. For example like the below example:

What I understand is that I need to make it so that PowerApps understands it should put the data not only according to month of a given year but so that "month 13" is essentially January 2021.

I hope this is understandable. Please do ask if I haven't clarified something.

Ping:

@CarlosFigueira, @mdevaney , @v-yutliu-msft 

[poweractivate]

@Anonymous 

As a general idea, do you think you could just calculate the date difference for the base case, and then do something only slightly different and handle it only if the start and end years are different? You should be able to calculate the date difference in a way that avoids the generalization you didn't like that made you want to avoid the simpler approach, although we would have to check in more detail, but we do not think this is actually as complicated as it would be made to appear here.

[Anonymous]

@poweractivate 

I am all ears. I haven't been able to figure out a simpler way myself unfortunately..