PowerApps – Display Year and Month from the SharePoint Data

This blog will discuss how to display distinct Years and Months from the SharePoint data.

Background

Assume you have SharePoint Data with a Date field like Created or Modified or Some other Date field. In Power apps, you want to display the Years and months based on Year selection.

SharePoint Data

Display Years

• Insert the Dropdown and name it “YearSelection”
• Set the Items property of the Year to

Sort(Int(Distinct(Definition,Text(StatusDate,”[$-en-US]yyyy”))),Value,SortOrder.Ascending)

Display Months

• Insert the Dropdown and name it “MonthSelection”
• When we use the formula to filter the month, we will get a delegation warning like below, and will not work.

Solution

• In SharePoint, Create another field called “StatusYearNumber”
• Make sure to create a Number field But not Calculated
• Set the Year (this new field) whenever Date field is updated

Note: Don’t use calculated column to strip Year. Still we receive delegation issue

• Set the items property of the MonthSelection dropdown to

Distinct(
Filter(
Definition,
StatusYearNumber = YearSelection.Selected.Value
),
Text(
StatusDate,
“[$-en-US]mmmm”
)
)

Result

To display the Year and Months, we need to create a new field to store the Year to filter it out.