cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

convert excel formula to powerbi function - calc start date from end date - duration (D/W/M/Y)

I need to check the duration to determine if it is days/weeks/months/years and then do a different forumla to get the start date.  I can do this in excel but I don't know what reserve words work in power bi.  If you know of a better way to do this with some reserved function or formula or measure in power bi please share with me.  I will be so grateful.  thanks. Michelle

Here is the actual completed formula but I first have to split the duration into two columns separating out the number.  Also I made functions for each type.

=IF(VLOOKUP(E3,USEFRM,2,TRUE)="CALCDAY",CALCDAY(C3,D3),IF(VLOOKUP(E3,USEFRM,2,TRUE)="CALCWEEK",CALCWEEK(C3,D3),IF(VLOOKUP(E3,USEFRM,2,TRUE)="CALCMONTH",CALCMONTH(C3,D3),CALCYEAR(C3,D3))))

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

Re: convert excel formula to powerbi function - calc start date from end date - duration (D/W/M/Y)

Thank you for replying... I figure out a way first using the DAX side then I went back to the power query editor and created a function to add the new column with the desired results.  Going back and forth between syntax is keeping me on my toes.

Below is the function I created that is working.

else if DurTime="week" or DurTime="weeks" then
else if DurTime="month" or DurTime="months" then
else if DurTime="year" or DurTime="years" then
else ""

2 REPLIES 2
Community Support Team

Re: convert excel formula to powerbi function - calc start date from end date - duration (D/W/M/Y)

In DAX, LOOKUPVALUE() is similar to VLOOPUP(), in addtion, I haven't found any reference about CALCWEEK(), CALCMONTH() and CALCYEAR(), however, you may refer to the time intelligence functions here.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Re: convert excel formula to powerbi function - calc start date from end date - duration (D/W/M/Y)

Thank you for replying... I figure out a way first using the DAX side then I went back to the power query editor and created a function to add the new column with the desired results.  Going back and forth between syntax is keeping me on my toes.

Below is the function I created that is working.

else if DurTime="week" or DurTime="weeks" then
else if DurTime="month" or DurTime="months" then
else if DurTime="year" or DurTime="years" then
else ""

Announcements

Community Highlights

Find out what's new in the Power BI Community!

Power Platform Summit North America

Register by September 5 to save \$200

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 106 members 1,497 guests
Recent signins: