Convert Excel fomula for use in Power BI

[asah]

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")))

[v-yuta-msft]

@asah ,

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

Regards,

Jimmy Tao

[asah]

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

[v-yuta-msft]

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

[asah]

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.