Forum Discussion
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 t...
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?
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.
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?
that works perfect.
thank you again for all your help.