In this blogpost, we will learn to add and subtract months to a date in Canvas App (Power App) using Power Fx formulas. Before we begin, ensure you subscribe to CRM Crate to remain informed about the latest developments in the Power Platform field.
Power FX is a robust formula language tailored for Microsoft’s Power Apps, particularly Canvas Apps, featuring an intuitive, Excel-like syntax that makes it accessible to users familiar with spreadsheet formulas. Its declarative nature allows users to define desired outcomes, with the system handling updates automatically, ensuring real-time responsiveness. Power FX seamlessly integrates with the Microsoft ecosystem, enabling data manipulation from sources like Dataverse and SharePoint to create interactive and customized user experiences. Promoting a low-code development approach, Power FX empowers citizen developers and business analysts to build applications without extensive programming knowledge, fostering innovation and agility within organizations. This democratization of app development reduces dependency on traditional development cycles, allowing teams to rapidly prototype, iterate, and deploy solutions, driving efficiency and productivity. Overall, Power FX bridges the gap between non-developers and professional developers, providing an intuitive yet powerful tool for creating dynamic, responsive, and scalable business applications within the Power Platform.
Adding & Subtracting months from a Date value using Power Fx formulas
You can use the EDate and EOMonth functions for dynamically adding & subtracting months from a date value in Canvas App. The EDate and EOMonth functions adjust a date by adding or subtracting a specified number of months.
EDate keeps the day the same unless it goes past the end of the new month. EDate is useful for calculating due dates or maturity dates that should fall on the same day of the month as the original date.
EOMonth, on the other hand, always sets the day to the last day of the new month after adjusting the month. This function is handy for finding due dates or maturity dates that should fall at the end of the month.
EDate( DateTime, NumberOfMonths )
DateTime is a required parameter and it should contain the date value to operate on.
NumberOfMonths is a also a required parameter and it should contain the number of months which needs to be added or subtracted from the date. (Use +Number for adding months and -Number for subtracting months).
EOMonth( DateTime, NumberOfMonths )
DateTime is a required parameter and it should contain the date value to operate on.
NumberOfMonths is a also a required parameter and it should contain the number of months which needs to be added or subtracted from the date. (Use +Number for adding months and -Number for subtracting months).
Example of EDate & EOMonth function
| Formula | Description | Function Outcome |
|---|---|---|
| EDate( Date(2024,6,8) , 1 ) | Adds a month to June 8, 2024, leaving the date unchanged. | Date(2024,7,8) |
| EDate( Date(2024,6,8) , -1 ) | Subtracts a month to June 8, 2024, leaving the date unchanged. | Date(2024,5,8) |
| EOMonth( Date(2024,6,8) , 1 ) | Adds a month to June 8, 2024, moving the date to the end of the month. | Date(2024,7,31) |
| EOMonth( Date(2024,6,8) , -1 ) | Subtracts a month to June 8, 2024, moving the date to the end of the month. | Date(2024,5,31) |
The below Canvas App animation demonstrates the working of EDate and EOMonth Power Fx function.
