How to use the function with in power apps

The With function in Power Apps evaluates a formula for a single record, allowing you to calculate values and perform actions. It is especially useful for improving the readability of complex formulas by breaking them into smaller, named sub-formulas.

Sintax

  • Record: The record to be acted upon. For named values, use the inline syntax { name1: value1, name2: value2, … }. 
  • Formula: The formula to be evaluated for the record. The formula can reference any field of the record directly.

				
					With( Record, Formula )
				
			

Examples

1. Simple arithmetic calculations: In this example, Result is calculated as 50 * 10, and then 50 is added to get 550.  

				
					With( { Result: 50 * 10 }, Result + 50 )
				
			

2. Date and Time Manipulation: This example extracts and manipulates date and time components. 

				
					With( { Today: Now() }, UpdateContext( { YYYY: Year(Today), MM: Month(Today), DD: Day(Today) } ) )
				
			

Advanced example of using with

Here it is used to calculate monthly dates between two estimated start and end dates of a project, and then filter a set of data based on those dates in order to show the monthly progress for each activity.  

We have the following Custom Page, where the objective is to be able to show the monthly progress of each activity of a project. 

This graphical representation is composed of two galleries:  

  • A first gallery to show the activities and a series of KPIs.   
  • A second nested gallery to show the monthly progress by activity.  

The interesting part of this development is how to obtain the dates and progress for each month.

To do this we use the following formula in the Items property of the gallery: 

				
					With(
    {
        sequenceYear: ForAll(
            Sequence(DateDiff(varProjectSelected.EstimatedStartDate, varProjectSelected.EstimatedEndDate, TimeUnit.Months) + 1),
            {
                CalculateDate: DateAdd(Date(Year(varProjectSelected.EstimatedStartDate), Month(varProjectSelected.EstimatedStartDate), 1), Value - 1, TimeUnit.Months)
            }
        )
    },
    ForAll(
        sequenceYear,
        First(
            Filter(
                colProgressPlan,
                ProjectID = GUID(varProjectSelected.ProjectID),
                ActivityID = ThisItem.'PM Activity',
                LocalPlanDate = CalculateDate
            )
        )
    )
)

				
			

Let’s explain the formula in detail:

1. Generate the monthly dates: we must obtain the months of duration of each of the activities.

A. Sequence: This function generates a sequence of numbers from 1 to the number of months between the start and end dates of the project. It calculates the difference in months between the two dates and adds 1 to include the start month. 

				
					DateDiff(varProjectSelected.EstimatedStartDate, varProjectSelected.EstimatedEndDate, TimeUnit.Months) + 1
				
			

B. DateDiff: Calculates the difference in months between the estimated start and end dates of the project. 
C. ForAll: Iterates over each number in the generated sequence. generada. 
D. DateAdd: Adds the number of months corresponding to each value in the sequence to the project start date to calculate the monthly dates.

				
					With(
    {
        sequenceYear: ForAll(
            Sequence(DateDiff(varProjectSelected.EstimatedStartDate, varProjectSelected.EstimatedEndDate, TimeUnit.Months) + 1),
            {
                CalculateDate: DateAdd(Date(Year(varProjectSelected.EstimatedStartDate), Month(varProjectSelected.EstimatedStartDate), 1), Value - 1, TimeUnit.Months)
            }
        )
    },
				
			

2. Filter the data to obtain the activity progress value for the current activity of the first gallery and by the corresponding date.  


A. ForAll: Iterate over each date calculated in sequenceYear. 
B. ColProgressPlan: It is a local collection where we have our progress by activity, project, month and progress.
C. Filter: Filters the colProgressPlan collection to find records that match the ProjectID, ActivityID and the calculated date (CalculateDate). 
D. First: Selects the first record that meets the filter criteria. This is useful if you expect only one record per combination of ProjectID, ActivityID and LocalPlanDate.

Achivement

This custom page is part of a project management application. With this development we allow users to track the progress of project activity on a monthly basis. This way we are making sure that the activities are being completed as planned.

Conclusion

The With function is a powerful tool in Power Apps that can help you create more efficient and easier to maintain applications. By breaking complex formulas into more manageable parts, you can improve both the readability and performance of your apps. 

Find out how we can take your business to the next level!

This website uses own and third party cookies for its proper functioning and for analytical purposes. It contains links to third-party websites with third-party privacy policies that you can accept or not when you access them. By clicking the Accept button, you agree to the use of these technologies and the processing of your data for these purposes.    More information
Privacidad