Cannot convert value '' of type Text to type Number.

[JasonUser000]

Hello, I have create a calendar date to coincide with fiscal year. It was recently working and just broke with a new upload of monthly data. Wondering how to fix this? 

[grantsamborn]

Hi @JasonUser000 

It appears you are using the result of RIGHT() (text type) as a number in the calculation.

Try replacing:

     RIGHT( Query[Fiscal Year], 2)

with:

     VALUE( RIGHT( Query[Fiscal Year], 2) )

[JasonUser000]

Hello,

Unfortunately that did not work. It is still showing as error.

[grantsamborn]

Is it still the same error?

Did you do the replacement in both places?

Is Query[Month_Number] numeric?

[JasonUser000]

Yes, I added to both places and Query[Month_Number] is numeric (1.2).

[grantsamborn]

This seems like it could be handled with a date table with [Fiscal Year] and [Fiscal Period] calculated for each [Date] ahead of time.

DimDate.m

Date Dimension Setup using M script.docx

[grantsamborn]

Not sure what it would be.

Here is an example with [Month_Number] as a whole number and [Fiscal Year] as text, using your calculated column.

Calendar Date problem.pbix

[JasonUser000]

I copied your exact DAX -  however it still is showing error although it works on your end.

[grantsamborn]

My guess is you have an invalid value for [Fiscal Year] .

I would start by looking for a blank row.

[JasonUser000]

My fiscal year Column is "F22, F23,..." Not 2022, 2023 which I cannot convert to number.