Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

Custom week number

I am new to PowerBi and trying to create the same week numbering that I have in excel for "fiscal week num".

My formula in excel is (based off of the date column): =ISOWEEKNUM(A2049-301)

is there a way for me to use this same logic in powerbi? if not, how would I go about accomplishing the same outcome?

thanks in advance

Tips and Tricks

Anonymous
7 years ago
Hi Anonymous

Looking at it Power BI doesn't seem to have ISOWeekNum however looking at your excel calculation it is just that days WeekNum minus 301 days.

Using that I created an Excel table like yours and imported it. I then created a column with
Calc Fiscal Week Num = WEEKNUM('Table1'[Date].[Date]-301,2)

and created a check column to see if the Calc Fiscal column matched the actual Fiscal Column but there was a difference when the year changed. I think the best way would be to import the table and connect it to your data.

I don't think there is any other way around this.

Why do you need a calculated figure rather than an imported reference table?

4 Replies

Anonymous
Not applicable
7 years ago
Hi Anonymous ,

you could import the same table you use in your excel document into Power Bi and use the same data, would that work for you? if you need help with this just let me know.

if this solved the issue don't forget to mark it as the solution.
- Anonymous
  Not applicable
  7 years ago
  true, and thats a good plan B but looking for more of an automated calculation or a formula similiar to the isoweek function in excel.
  - Anonymous
    Not applicable
    7 years ago
    Hi Anonymous
    
    Looking at it Power BI doesn't seem to have ISOWeekNum however looking at your excel calculation it is just that days WeekNum minus 301 days.
    
    Using that I created an Excel table like yours and imported it. I then created a column with
    Calc Fiscal Week Num = WEEKNUM('Table1'[Date].[Date]-301,2)
    
    and created a check column to see if the Calc Fiscal column matched the actual Fiscal Column but there was a difference when the year changed. I think the best way would be to import the table and connect it to your data.
    
    I don't think there is any other way around this.
    
    Why do you need a calculated figure rather than an imported reference table?