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

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

excelformula.jpg

 

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
Highlighted
mcraven1845 Frequent Visitor
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.

 

let StartUpgradeDate = (DurTime,DurCnt,UpgradeRpDate) => if DurTime="day" or DurTime="days" then
                                   Date.AddDays(UpgradeRpDate, (DurCnt*-1))
                           else if DurTime="week" or DurTime="weeks" then
                                   Date.AddWeeks(UpgradeRpDate, (DurCnt*-1))
                           else if DurTime="month" or DurTime="months" then
                                  Date.AddMonths(UpgradeRpDate, (DurCnt*-1))
                           else if DurTime="year" or DurTime="years" then
                                  Date.AddYears(UpgradeRpDate, (DurCnt*-1))
                           else ""
in StartUpgradeDate

2 REPLIES 2
Community Support Team
Community Support Team

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

@mcraven1845 ,

 

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.

Highlighted
mcraven1845 Frequent Visitor
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.

 

let StartUpgradeDate = (DurTime,DurCnt,UpgradeRpDate) => if DurTime="day" or DurTime="days" then
                                   Date.AddDays(UpgradeRpDate, (DurCnt*-1))
                           else if DurTime="week" or DurTime="weeks" then
                                   Date.AddWeeks(UpgradeRpDate, (DurCnt*-1))
                           else if DurTime="month" or DurTime="months" then
                                  Date.AddMonths(UpgradeRpDate, (DurCnt*-1))
                           else if DurTime="year" or DurTime="years" then
                                  Date.AddYears(UpgradeRpDate, (DurCnt*-1))
                           else ""
in StartUpgradeDate

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 398 members 3,722 guests
Please welcome our newest community members: