cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jasprice Frequent Visitor
Frequent Visitor

Use PREVIOUSYEAR function on a column that only contains a Year value

Hello,

 

I am trying to create a "Previous Year" measure using the PREVIOUSYEAR function but get the following error since my 'Fiscal Year' column is actually a Text data type. 

 

"A column specified in the call to function 'PREVIOUSYEAR' is not of type DATE. This is not supported."

 

Is there a work around for this?   Possibly creating a fake date format for the 'Fiscal Year' column?

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Use PREVIOUSYEAR function on a column that only contains a Year value

All time intelligence functions require the use of an additional date table that contains an unbroken non-repeating series of dates covering the period required by your data. This table should have a relationship to a matching date column in your fact table. In the case of your monthly data perhaps a column with the end date of the month in question.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
4 REPLIES 4
Super User
Super User

Re: Use PREVIOUSYEAR function on a column that only contains a Year value

All time intelligence functions require the use of an additional date table that contains an unbroken non-repeating series of dates covering the period required by your data. This table should have a relationship to a matching date column in your fact table. In the case of your monthly data perhaps a column with the end date of the month in question.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
Community Support Team
Community Support Team

Re: Use PREVIOUSYEAR function on a column that only contains a Year value

Hi @jasprice,

 

From the prompt error, we can know that PREVIOUSYEAR function only support date type values. So, why don't you change the data type for that column in PowerBI. How is your 'Fiscal Year' column look like?

 

Also, as KHorseman mentioned, you need an additional date table, having a relationship to a matching date column in your fact table. 

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jasprice Frequent Visitor
Frequent Visitor

Re: Use PREVIOUSYEAR function on a column that only contains a Year value

Hi Yuliana,

 

I created a Date table lookup and joined it with my main (fact) table.   When I created the calculated measure it seems to be fine except when I add it to a Pivot Table.   When I do so it is blank.   Any idea why that would be?

 

This is the formula:

 

Previous Year Fees:=CALCULATE(SUM([Fees]),PREVIOUSYEAR(DimDate[Date]))

 

Jason

jasprice Frequent Visitor
Frequent Visitor

Re: Use PREVIOUSYEAR function on a column that only contains a Year value

Nevermind, found my error.  I wasn't using the DimDate{Date) in my Pivot Table.  Was using the date in the "fact" table.