Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
asah
Helper I
Helper I

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
v-yuta-msft
Community Support
Community Support

@asah ,

 

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

 

Regards,

Jimmy Tao

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

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.