Edit Query - create calculated date from specific field for all rows

[Anonymous]

Hi, I'm trying to create a calculated column in Edit Query (Not an actual measure) for all rows of data that I'm bringing in from an excel file.  Ideally, the formula would calculate the latest possible day of the year (12/31/XX) of the column name 'Service Through Date'. For example:    If the Service Through Date is 5/1/2019, I'd need the result of the new column for this row to be 12/31/2019. If the Service Through Date is 10/13/2018, I'd need the result of the new column for this row to be 12/31/2018. What is the proper Created Calculated Column formula for this? Thank you, Andrew

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

First, you should know that the difference between calculate column and custom column in power bi.

https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/

You could use DAX Function to create  a column column(New column), and use M language to create a custom column in edit query.

second, in edit query, you could just use Date.EndOfYear to get your requirement.

=Date.EndOfYear([Service Through Date])

https://docs.microsoft.com/en-us/powerquery-m/date-endofyear

Result:

Regards,

Lin

[amitchandak]

@Anonymous ,

New Date = date(year([column]),12,31)

There is function endofyear, but that will work on continuous date

[Anonymous]

Thank you for the help but I have an error.

I entered the formula:

New Formula = date(year([Service Through Date]),12,31)

I have an expression error that reads:  "Expression Error:  The name 'date' wasn't recognized.  Make sure it's spelled correctly."

I double checked that [Service Through Date] is already in a date format and that it is spelled correctly.  I wonder if the command 'Date' in the formula should be something else?

Thank you!

[harshnathani]

Hi @Anonymous ,

You can try this to troubleshoot. Also, assuming that [Service Through Date] is a column.

Try this measure.

New Formula =
var _year = YEAR('Table'[Service Through Date])

RETURN
DATE(_year,12,31)

Regards,

HN