Dataverse calculated column to extract the year from date

(0) Share

Report

Posted on by WebPortal

2,297

This seems the easiest thing in the world: a calculated column in dataverse like;

Year(MyDate)

But it returns the error:

Year cannot be performed on this input without a time zone conversion, which is not supported in formula columns.

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Mark Nanneman 991 Moderator on at

Like (1)

Report
Copy link

Link copied!

I never found a way to accomplish this when I wanted to months ago--but there were other ways to do what I wanted that didn't even require the column. If you absolutely must have a year column, you can create a plugin or flow to auto populate it off of your date column when the record is created or updated (to handle new records going forward)--and you can retroactively bulk populate your year column by doing an export to excel, using some quick excel formulas to fill in that column, and then importing the whole table back into dataverse for updates.

Just some thoughts.

Was this reply helpful? Yes No
ronaldwalcott 3,862 Moderator on at

Like (0)

Report
Copy link

Link copied!

Year function only works on UTC dates

Was this reply helpful? Yes No
WebPortal 2,297 on at

Like (0)

Report
Copy link

Link copied!

@Mark Nanneman

Sure, I have the year in Power Fx and I use it to filter a large Dataverse table.

The issue is delegation. Thus, having the year on a column would help.

Was this reply helpful? Yes No
Suggested answer

truc binh 17 on at

Like (1)

Report
Copy link

Link copied!

You can change field MyDate to Date Only or Timezone Independent. Then you can apply the Year function in Formula column

Was this reply helpful? Yes No
WebPortal 2,297 on at

Like (0)

Report
Copy link

Link copied!

@truc binh

Thanks, but how may I do that?

Here are the column specifications:

Was this reply helpful? Yes No
Mark Nanneman 991 Moderator on at

Like (1)

Report
Copy link

Link copied!

There's at least one option that works on large data sources with no delegation issues.

I have a demo I can share that uses a gallery of years to work like pages or filter selections. It's created just using a simple sequence() function.

Years Gallery:

Formula for the dataverse gallery you want to filter by year:

With(

{

startDate: DateTimeValue(Text(Date(gal_years_filter.Selected.Value,1,1),DateTimeFormat.UTC)),

stopDate: DateTimeValue(Text(Date(gal_years_filter.Selected.Value,12,31),DateTimeFormat.UTC))

},

Sort(

Search(

Filter(

'Large Date Tables',

'Long Date' >= startDate And 'Long Date' <= stopDate

),

txt_search.Value,

Name

),

'Long Date',

SortOrder.Descending

)

)

I've done versions of these where you can select multiple years from from your year gallery and it just finds the max and min from that collection to generate the start and stop filter dates.

Example of the filtering:

works very quickly,almost instant

If this helped you, please click "Does this answer your question" and give it a like to help others in the community (+ close the ticket)!

Power Platform Developer | LinkedIn: Mark Nanneman | Blog: Power Stuff | YouTube: Mark's Power Stuff |

Was this reply helpful? Yes No
Suggested answer

ronaldwalcott 3,862 Moderator on at

Like (1)

Report
Copy link

Link copied!

If you are forced to here is a weird way to do it.

Add a new datetime column TestDateConstant which will default to January 1st 1900.

The date that I want to extract the year from is called TestDate

Create a formula field

Text(DateDiff(TestDateConstant, TestDate, TimeUnit.Years) + 1900, "####")

This gives you a text field with the year

Was this reply helpful? Yes No
Verified answer

truc binh 17 on at

Like (1)

Report
Copy link

Link copied!

Hi @WebPortal,

You can set in the box related to timezone below.

Was this reply helpful? Yes No