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

[jasprice]

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.

[v-yulgu-msft]

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

[jasprice]

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]

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

[KHorseman · Accepted solution]

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.