Forum Discussion
Custom Column Query
You can't concatenation text with numbers. Convert the number first.
Number.ToText([YearNum])
Thank you this worked, however this is ignoring double numbers for instance I have added to the query so that if th Reporting Month is greater than or equal to 8 however this does not work if the number is 10, 11, 12 etc... Might you have any ideas why please?
Sorry, I don't understand. Can you post your new formula?
Sorry Matt,
My formula is now:
if Number.ToText([ReportingMonthNum]) < "8" then "FY" & Number.ToText([YearNum]) else if Number.ToText([ReportingMonthNum]) >= "8" then "FY" & Number.ToText(Value.Add([YearNum], 1)) else ""
In the formula it should be that if the "ReportingMonthNum" is greater or equal to 8 then the value should be FY & the year number plus 1, this works for months 8 & 9 however it does not work for months 10, 11 or 12:
It appears to be ignoring numbers with anything more than two digits.
The issue is just after your second "if". You should not be using Number.ToText for the comparator, just for the concatenation.
Still does not work sorry, my query is now:
if [ReportingMonthNum] < "8" then "FY" & Number.ToText([YearNum]) else if [ReportingMonthNum] >= "8" then "FY" & Number.ToText(Value.Add([YearNum], 1)) else ""
The Year Number column has a type of whole number as did the reporting month but I have changed this back to the default now.
Column Values