Help with making a PowerPivot formula work with Power BI

[dvnnvd]

Hi Guys,

I currently use PowerPivot and have the below code which creates a custom column with calculated values:

=if([month_logged] >= 8,[year_logged]&"-"&right([year_logged]+1,2), [year_logged]-1&"-"&right([year_logged],2)) 

This code takes the values of the "month_logged" and "year_logged" columns already in the table and outputs an acedemic year calculation in a column formatted like 2012-2013. This works well in PowerPivot without any issues.

I'm trying to do this in Power BI, but after several hours of trying and several hours of looking at DAX guides I feel like I'm no further forward.

I've determined that the Power BI formula needs to be changed like the below so that it contains "then" and "else" statements, but I still can't get this working... This results in "Expression.Error: The name 'right' wasn't recognized.  Make sure it's spelled correctly."

if (ABC[month_logged]) >= 8 then (ABC[year_logged])&"-"&right((ABC[year_logged])+1,2) else (ABC[year_logged])-1&"-"&right((ABC[year_logged]),2)

Anyone have any suggestions on how to take the working PowerPivot code and format this in a way that would make it work with Power BI?

[prakash_gautam]

Hello,

You can use Text.Range and you might need Number.ToText as well. Something like:

if ([month_logged]) >= > then Number.ToText([year_logged]) &"-"& Text.Range(Number.ToText([year_logged]+1),2) 
 else ([year_logged])-1 &"-"& Text.Range(Number.ToText([year_logged]),2)

Thanks

[dvnnvd]

@prakash_gautam

Thank you so much for your reply. I am now much further forward.

Interestingly, some of the data produces the correct information but the vast majority returns "error" with the below explanation:

Expression.Error: We cannot apply operator & to types Number and Text.
Details:
    Operator=&
    Left=2010
    Right=-

All of my data is the same, so I'm not sure why the customised column is displaying correctly for some but not others. I have tried formatting both the columns involved in the formula to text but this makes no differance. 

[prakash_gautam]

@dvnnvd

Can you please share sample data? Myself or someone else might be able to provide an explanation.

Thanks