cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mcraven1845 Frequent Visitor
Frequent Visitor

custom function creates but expression error when add column/invoke custom function getting

I created a custom function to get the Year part of a date and then use that custom function inside of another more complex function.  Both functions create successfull but when I try to add column, invoke custom function it gives me 

 

An error occurred in the ‘FYR1’ query. Expression.Error: We cannot convert the value #date(2019, 7, 1) to type Function.
Details:
Value=7/1/2019
Type=Type

 

I'm new to this M language but I have had experience in programming.  Your help is appreciated!

 

Here is the complex function

 

let FYR1 = (CYR,UpgradeRpDate,CurrentMaint,UpgradeCost) => if CYR([UpgradeRpDate])="TRUE" then [CurrentMaint]+[UpgradeCost] else [CurrentMaint]
in FYR1

 

thanks in advance for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
mcraven1845 Frequent Visitor
Frequent Visitor

Re: custom function creates but expression error when add column/invoke custom function getting

Hello, Thank you for your offer to help.  Chris Webb from Cross Join was able to help me.  I'll include the solution below for others to benefit from.

 

The correct and successful function is as follows - 

 

let Fiscalyr1 = (UpgradeRpDate,CurrentMaint,UpgradeCost) => if Date.IsInCurrentYear(Date.Year(UpgradeRpDate))=Date.Year(UpgradeRpDate) then

                                                                 CurrentMaint+UpgradeCost

                                                            else CurrentMaint

in Fiscalyr1

 

I was using square brackets incorrectly in the original one.  You use square brackets when you’re calling the function because you’re getting values from the various columns on the current row. Inside the function, however, you’re working with function parameters and not columns on a row in a table.

 

This is now a solved issue.  thanks again for reaching out to help!

View solution in original post

6 REPLIES 6
Community Support Team
Community Support Team

Re: custom function creates but expression error when add column/invoke custom function getting

Hi @mcraven1845,

 

I'm not so clear for your scenario(e.g. custom function parameter type, original table data structure), can you please provide more detailed information about these?

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
mcraven1845 Frequent Visitor
Frequent Visitor

Re: custom function creates but expression error when add column/invoke custom function getting

Hello Xiaoxin, 

 

Thank you for your reply.  I appreciate your willingness to try and understand to help me.

 

Let me start with what I am trying to accomplish first and then maybe you can tell me a better/easier way to handle it.

 

I successfully created an excel worksheet to take the provided current year maintenance cost of equipment along with the next year maintenance cost to produce the projected cost for the next five years.  I used 4 of the 10 provided columns (2,3,9,10) below.  Here is a simple example to follow:

 

              Current Yr        Next year     --------------------Fiscal Yr Cost----------------------------

Type       Maint cost      Maint cost    2019           2020            2021             2022              2023            update date     upgrade cost

software     1,000             1,100         1,000          1,100           1,760            1,331             1,464              7/1/2021             550

 

to calculate cost for each fiscal year the formulas are below

 

 IF(YEAR(NOW())=[],[Current Year Maintenance]+[Upgrade/Replace Cost],[Current Year Maintenance])

 

IFERROR(next yr maint cost/ current yr maint cost,0)

 

2019 Fiscal Yr Cost à  IF(YEAR(NOW())=YEAR(update date),current yr maint cost+upgrade cost,current yr maint cost)

2020 Fiscal Yr Cost -> IF(YEAR(NOW())+1=YEAR(update date),( current yr maint cost * IFERROR(next yr maint cost/ current yr maint cost,0)

)+ upgrade cost, current yr maint cost * IFERROR(next yr maint cost/ current yr maint cost,0))

2021 Fiscal Yr Cost -> IF(YEAR(NOW())+2=YEAR(update date),( current yr maint cost * IFERROR(next yr maint cost/ current yr maint cost,0)^2)+ upgrade cost, current yr maint cost * IFERROR(next yr maint cost/ current yr maint cost,0)^2)

 

I’m having a hard time creating this formula as a function that works in powerBI or the M language which ever this is considered.  I am able to create the function successfully but when I go to add column>invoke custom function (which is available) it gives me the error.  

 

thanks for your help!

 

Community Support Team
Community Support Team

Re: custom function creates but expression error when add column/invoke custom function getting

HI @mcraven1845,

 

According to your discretion, it seems like you need to calculate result based on current column value. It possible to get current column label as parameter to calculate in dax formula.

 

Can you lease share a pbix file with same data structure fake data so that I can test to coding formula on it?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
mcraven1845 Frequent Visitor
Frequent Visitor

Re: custom function creates but expression error when add column/invoke custom function getting

I would love to share it but I'm not sure exactly what the pbix file is or how to share it here? 

Community Support Team
Community Support Team

Re: custom function creates but expression error when add column/invoke custom function getting

HI @mcraven1845

 

You can upload to onedrive or google drive and share link here. If your data are from external datasource, you can copy part of your records to excel sheet and pack it with your pbix file for share.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
mcraven1845 Frequent Visitor
Frequent Visitor

Re: custom function creates but expression error when add column/invoke custom function getting

Hello, Thank you for your offer to help.  Chris Webb from Cross Join was able to help me.  I'll include the solution below for others to benefit from.

 

The correct and successful function is as follows - 

 

let Fiscalyr1 = (UpgradeRpDate,CurrentMaint,UpgradeCost) => if Date.IsInCurrentYear(Date.Year(UpgradeRpDate))=Date.Year(UpgradeRpDate) then

                                                                 CurrentMaint+UpgradeCost

                                                            else CurrentMaint

in Fiscalyr1

 

I was using square brackets incorrectly in the original one.  You use square brackets when you’re calling the function because you’re getting values from the various columns on the current row. Inside the function, however, you’re working with function parameters and not columns on a row in a table.

 

This is now a solved issue.  thanks again for reaching out to help!

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 149 members 1,884 guests
Please welcome our newest community members: