Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
Proud to be a Super User!
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
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
Nevermind, found my error. I wasn't using the DimDate{Date) in my Pivot Table. Was using the date in the "fact" table.
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |