cancel
Showing results for
Did you mean:
asah 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

## Re: Convert Excel fomula for use in Power BI

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

Regards,

Jimmy Tao

Highlighted
asah 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.

 Date Week 02/26/18 01 02/27/18 01 02/28/18 01 03/01/18 01 03/02/18 01 03/03/18 01 03/04/18 01 03/05/18 02

Thank you,

Anirudh Community Support Team

## Re: Convert Excel fomula for use in Power BI

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.

asah 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.

Announcements #### 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

Make sure you didn't miss any of the things that happened in the community in January! Top Solution Authors
Top Kudoed Authors
Users online (1,420)