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
Anonymous
Not applicable

Date table with fiscal week numbers

Hi 

 

Can i get some advice on how to write a date table with fiscal week number please . our company fiscal week starts on the first week of april and ends on the last week of march the following year. I have import a excel file with the following columns 

date = 31/mar/2019 to 1/apr/2024

quarter start = 31/mar/2019

quarter finish = 29/jun/2019 and so on for the 4 quarters

fiscal year = FY + year

standard week number = used add column from example and changed value to week number in power query

month number = used add column from example and changed value in power query

 

is there a way to add the fiscal week number as a new column ? tried to use the week number - 12 but the 31/mar/2019 displayed as week 1 and the 1/apr/2019 displayed as week 2 and end up have 53 weeks which should be 52 weeks. I am very new to this and look farword for your help.

 

 

Many Thanks

 

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

14 REPLIES 14
v-kelly-msft
Community Support
Community Support

Hi  @Anonymous ,

 

Which day should be the first day of a week?Sunday or Monday?

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

Anonymous
Not applicable

Hi @v-kelly-msft 

 

Thanks for the reply and it's been sorted by @mwegener .

 

 

Thanks

Anonymous
Not applicable

Hi @mwegener 

 

Just tried with the DAX and somehow it only shows the week of 35, Have add column using new column from the data view below is screen shot from BI desktop 

David_DF_0-1635111491842.png

and below is the DAX that i pasted 

David_DF_1-1635111547134.png

 

Have tried to paste the M code but result is pretty simpler. could you please have a look. 

 

Thanks

 

 

Hi @Anonymous ,

 

somehow I misunderstood the requirement, but have a look at the attached file.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

Hi @mwegener 

 

 

Apologies for the late reply as I can only learn this over the weekend . 

 

I have watched a video on youtube and foloowed steps and done a calander and used your DAX code to get the FW numbers sorted . 

 

below is the link that i watched 

Date Dimension in Power BI with Financial or Fiscal Columns - YouTube

 

will have a play around and see if anything else needs to add . 

 

Many thanks for your help and much appreciated .

 

Cheers

Hi @Anonymous ,

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

HI I HAVE THE SAME QUESTION, but i cant view the solution? please thanks

Anonymous
Not applicable

the formula i need is for power query not power bi please

Anonymous
Not applicable

Have created a week number column and tried to change the letters to upper case and still get the same error massage .

 

 

Thanks

Anonymous
Not applicable

Hi @mwegener 

 

Thanks for the reply and i have tried but there is an error message says

 

Token Eof expected and not sure what happened as i did copy your code & pasted in. Screen shot below.

David_DF_0-1635101194629.png

 

Thanks

 

You have a space between __ and FW.  Delete that.

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Hi @mahoneypat 

 

There is a error message when delete to space 

David_DF_0-1635127204865.png

 

Maybe somewhere along the line that i've done wrong but just trying to have a calander that will have the date,fiscal week,fiscal quarter & fiscal year on it. 

 

Thanks

 

Hi @Anonymous ,

 

the code is not M (Power Query), but DAX.

 

Maybe this helps

let
    Source = List.Range({0..52}, 1),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Week Number"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Week Number", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Fiscal Week Number", each if [Week Number] - 32 + 1 <= 0 then 52 + [Week Number] - 32 + 1 else [Week Number] - 32 + 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Fiscal Week Number", Int64.Type}})
in
    #"Changed Type1"

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Hi @Anonymous ,

 

take a look at this solution.

https://community.powerbi.com/t5/Desktop/Creating-a-fiscal-week-column/m-p/556549

DAX:

Fiscal Week = 
VAR __fw = [WeekNum] - 32 + 1
RETURN IF(__fw<=0,52+__fw,__fw)

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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.

Top Solution Authors
Top Kudoed Authors