Forum Discussion

dvnnvd's avatar
dvnnvd
Regular Visitor
9 years ago

Help with making a PowerPivot formula work with Power BI

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?

Need Help

3 Replies

  • prakash_gautam's avatar
    prakash_gautam
    Resolver II
    9 years ago

    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's avatar
      dvnnvd
      Regular Visitor
      9 years ago

      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's avatar
        prakash_gautam
        Resolver II
        9 years ago

        dvnnvd

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

         

        Thanks