Unanswered

Adding data to Month-fields according to StartDate and duration from Budget of Project

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi all,

My application is based on an excel spreadsheet with a Maintable, in which many different data (items) can get added from the PowerApps application in the editform. Each item/project has a variety of data such as:

NameClient, StartDate, DurationInDays, EndDate(Calculated by Startdate+DateDiff(durationinDays), ClientBudget, Jan2020,Feb2020,Mar2020, etc.

Ideally, I would like to be able to add the StartDate, DurationInDays and ClientBudget, after which the months that the projects spans over gets added into the months. As an example:

If the projects data is:

StartDate: 3-March

DurationInDays: 60

ClientBudget: 10.000

Then the data fields:

Jan2020: 0

Feb2020: 0

Mar2020: 5.000

Apr2020: 5.000

May2020: 0

-//-

I have been trying many things and looked around for ways to use the If() function to determine which dates the project spans over, but haven't found any solution.

Could anyone please help me out with this?

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

Sort by

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@CarlosFigueira, would you be able to guide me in a direction of solving this problem? I would be very grateful!

Was this reply helpful? Yes No
v-yutliu-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous ,

Do you want to transfer data from table1 to table2?

I've made a similar test:

1)table1:

ClearCollect(data1,{project:"project1",StartDate:Date(2020,3,3),DurationInDays:60,ClientBudget:10000},{project:"project2",StartDate:Date(2020,5,3),DurationInDays:90,ClientBudget:20000})

2)table2:

ClearCollect(data2,{project:"project1",Jan2020:0,Feb2020:0,Mar2020:0,Apr2020:0,May2020:0,June2020:0,July2020:0},{project:"project2",Jan2020:0,Feb2020:0,Mar2020:0,Apr2020:0,May2020:0,June2020:0,July2020:0})

3)use table1 to update table2:

ForAll(data1, Patch(data2,LookUp(data2,project=data1[@project]), { Mar2020:If(3>=Month(StartDate)&&3<=Month(DateAdd(StartDate,DurationInDays,Days)),ClientBudget/(DurationInDays/30),0), Apr2020:If(4>=Month(StartDate)&&4<=Month(DateAdd(StartDate,DurationInDays,Days)),ClientBudget/(DurationInDays/30),0), May2020:If(5>=Month(StartDate)&&5<=Month(DateAdd(StartDate,DurationInDays,Days)),ClientBudget/(DurationInDays/30),0), June2020:If(6>=Month(StartDate)&&6<=Month(DateAdd(StartDate,DurationInDays,Days)),ClientBudget/(DurationInDays/30),0), July2020:If(7>=Month(StartDate)&&7<=Month(DateAdd(StartDate,DurationInDays,Days)),ClientBudget/(DurationInDays/30),0) } ) )

//I just use mar/apr/may/june/july as example, you could calculate other months like this. Just need to make a little change.

For example: Aug:

Jug2020:If(8>=Month(StartDate)&&8<=Month(DateAdd(StartDate,DurationInDays,Days)),ClientBudget/(DurationInDays/30),0)

Sep:

Sep2020:If(9>=Month(StartDate)&&9<=Month(DateAdd(StartDate,DurationInDays,Days)),ClientBudget/(DurationInDays/30),0)

,....

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-yutliu-msft , thank you for the response. This looks very promising.

To the first question: We are using one single table as can be seen below, does this have an effect on how the approach is? Can we still use the Patch() function for one table?

The interface of the editform in which we add the data looks like this:

Maybe I can add the Patch() function to the "SaveButton"?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
@v-yutliu-msft,

So I tried the function you used for the same table with the following OnSelect property of a button:

Patch( Maintable_SPM,Defaults(Maintable_SPM), {Jan2020:If(1>=Month(DateValue('Estimated Finish Time_DataCard2'.DataField))&&1<=Month(DateAdd(DateValue('Estimated Finish Time_DataCard2'.DataField),Value(DurationDaysDataCardValue.Text),Days)),CurrentBudgetDataCard/(DurationDaysDataCardValue/30),0), Feb2020:If(2>=Month(DateValue('Estimated Finish Time_DataCard2'.DataField))&&2<=Month(DateAdd(DateValue('Estimated Finish Time_DataCard2'.DataField),Value(DurationDaysDataCardValue.Text),Days)),CurrentBudgetDataCard/(DurationDaysDataCardValue/30),0), etc... etc... })

Whenever I update the Budget, starttime,DurationInDays it updates from January and divides by the Duration in days.

As an example I inserted: StartDate: 1 January2020, DurationInDays: 59, CurrentBudget: 1000, then this shows in the table:

It divides the CurrentBudget in two, but it does not insert the CurrentBudget for February.

Likewise if I start in March, it will still start from January. Did I do something wrong in the formula?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@v-yutliu-msftdid you have a chance to look at my comments?

Was this reply helpful? Yes No
v-yutliu-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous ,

The formulas that I posted is used to use one table's data to update another.

While in your issue, these fields are in the same table.

So the solution that I provided doesn't work.

Based on the pics, do you want to fill in data in these three fields:

StartDate: 3-March

DurationInDays: 60

ClientBudget: 10.000

then other fields will be filled in value automatically?

If so, I've made a similar test for your reference:

1)insert a datepicker named DatePicker1 for StartDate

a textinput named Textinput1 for DurationInDays

a textinput named Textinput2 for ClientBudget

12 textinputs named Textinput3_1,Textinput3_2,.....Textinput3_12 for Jan2020, Feb2020,....,Dec2020

2)Set Textinput3_1's Text: (Jan2020)

If(1>=Month(DatePicker1.SelectedDate)&&1<=Month(DateAdd(DatePicker1.SelectedDate,Value(TextInput1.Text),Days)),Value(TextInput2.Text)/(Value(TextInput1.Text)/30),0)

Textinput3_2's Text: (Feb2020)

If(2>=Month(DatePicker1.SelectedDate)&&2<=Month(DateAdd(DatePicker1.SelectedDate,Value(TextInput1.Text),Days)),Value(TextInput2.Text)/(Value(TextInput1.Text)/30),0)

Textinput3_3's Text: (Mar2020)

If(3>=Month(DatePicker1.SelectedDate)&&3<=Month(DateAdd(DatePicker1.SelectedDate,Value(TextInput1.Text),Days)),Value(TextInput2.Text)/(Value(TextInput1.Text)/30),0)

....

Textinput3_12's Text: (Dec2020)

If(12>=Month(DatePicker1.SelectedDate)&&12<=Month(DateAdd(DatePicker1.SelectedDate,Value(TextInput1.Text),Days)),Value(TextInput2.Text)/(Value(TextInput1.Text)/30),0)

Then, after you select date in the datepicker and fill in data in DurationInDays and ClientBudget, the other 12 fields will be filled in data automatically.

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Solved. (RoundUp function)

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
@v-yutliu-msft, another problem I am occuring is if a project spans from say December 2020 until January 2021. Then the following happens:

I have tried to change the Default property of each of the months to the follow:

If(Toggle3.Value = true, If(2020=Year(EstimatedStart.SelectedDate)&&11>=Month(EstimatedStart.SelectedDate)&&11<=Month(DateAdd(EstimatedStart.SelectedDate,Value(DurationDaysDataCardValue.Text),Days)), (If((Value(DurationDaysDataCardValue.Text)<=30),(Value(CurrentBudgetDataCard.Text)), (Value(CurrentBudgetDataCard.Text)/(Value(DurationDaysDataCardValue.Text)/30)))),0),false)

I tried to add: 2020=Year(EstimatedStart.SelectedDate)&&.....AND... 2021=Year(EstimatedStart.SelectedDate)&&

When the calender year goes into 2021, none of the Months get calculated.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@v-yutliu-msftLast time bothering you I promise 🙂

Was this reply helpful? Yes No