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

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.

Highlighted
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