Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
EmmaDilemma
New Member

How do I type this excel formula into Powerquery in Powerbi

Hi clever people,

 

I need to transfere this simple excel formula into a custom column in Powerquery in Powerbi . 

(J cell is Departure Date, I cell is Cover Start Date,BG cell is Month of Earning)

 

=IFERROR(IF(J470460<BG470460,0.2,SUM(0.2+SUM(BG470460-J470460)/SUM(J470460-I470460)*0.2)),0)

 

Can someone please tell me how I type this - I have got this far so far but am stuck at the SUM part....

 

EmmaDilemma_0-1686141274517.png

 

1 ACCEPTED SOLUTION
JamesRobson
Resolver II
Resolver II

So IF statements are written slightly different "if" "then" "else"  and you dont need to define a SUM like you do in Excel so try this...

 

if ([Departure Date] < [Month of Earning] then 0.2 else ([Month of Earning] - [Departure Date]) / ([Departure Date] - [Cover Start])

 

Let me know if that works for you.

 

Thanks,

View solution in original post

2 REPLIES 2
EmmaDilemma
New Member

Thank you

JamesRobson
Resolver II
Resolver II

So IF statements are written slightly different "if" "then" "else"  and you dont need to define a SUM like you do in Excel so try this...

 

if ([Departure Date] < [Month of Earning] then 0.2 else ([Month of Earning] - [Departure Date]) / ([Departure Date] - [Cover Start])

 

Let me know if that works for you.

 

Thanks,

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors