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

Convert Excel fomula for use in Power BI

Hello everone,

Could someone kindly help in converting the following excel formulas for use with Power BI. They calculate week nos. used in my organisation based on date provided as input in the mm/dd/yyyy format.

 

Formula 1: (where A1 is the date input and Monday is the first day of the week)

=IF(AND(INT((A1-WEEKDAY(A1,2)-DATE(YEAR(A1)-(MONTH(A1)<3),3,1))/7)+2=53,TEXT("1 - Mar - "&YEAR(A1),"dddd")<>"Sunday"),"01",TEXT(INT((A1-WEEKDAY(A1,2)-DATE(YEAR(A1)-(MONTH(A1)<3),3,1))/7)+2,"00"))

 

Formula 2: (where A1 is the date input and Sunday is the first day of the week)

=IF(AND(INT((A1-WEEKDAY(A1)-DATE(YEAR(A1)-(MONTH(A1)<3),3,1))/7)+2=53,TEXT("1 - Mar - "&YEAR(A1),"dddd")<>"Sunday"),YEAR(A1)&"01",IF(AND(MONTH(A1)>=1,MONTH(A1)<3),YEAR(A1)-1&TEXT(INT((A1-WEEKDAY(A1)-DATE(YEAR(A1)-(MONTH(A1)<3),3,1))/7)+2,"00"),YEAR(A1)&TEXT(INT((A1-WEEKDAY(A1)-DATE(YEAR(A1)-(MONTH(A1)<3),3,1))/7)+2,"00")))

 

4 REPLIES 4
Community Support Team
Community Support Team

Re: Convert Excel fomula for use in Power BI

@asah ,

 

Could you please share sample data and give the expected result?

 

Regards,

Jimmy Tao

asah Frequent Visitor
Frequent Visitor

Re: Convert Excel fomula for use in Power BI

Hi Jimmy,

I have provided the sample data below. The Week column has been populated by applying the formula to the date column which is what I am expecting to achieve in Power BI. My source data will give me just the date and I want the week numbers to be calculated by using the equivalent of the excel formula.

 

DateWeek
02/26/1801
02/27/1801
02/28/1801
03/01/1801
03/02/1801
03/03/1801
03/04/1801
03/05/1802

 

Thank you,

Anirudh

Community Support Team
Community Support Team

Re: Convert Excel fomula for use in Power BI

@asah ,

 

You may use WEEKNUM() function to achieve the weeknum in power bi:

https://docs.microsoft.com/en-us/dax/weeknum-function-dax

 

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
asah Frequent Visitor
Frequent Visitor

Re: Convert Excel fomula for use in Power BI

Jimmy, apologies for my delayed response.

The WEEKNUM() function does not solve the problem as I am not after the regular calendar weeks. These are custom weeks which are produced based on the formula which I mentioned initially.

I have managed to workaround this problem by loading a table in Power BI with the dates and the corresponding weeks generating using Excel and am looking up the values from this table where required.

Thanks for your help.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors