Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi @Anonymous ,
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for 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!
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
and below is the DAX that i pasted
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.
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.
HI I HAVE THE SAME QUESTION, but i cant view the solution? please thanks
the formula i need is for power query not power bi please
Have created a week number column and tried to change the letters to upper case and still get the same error massage .
Thanks
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.
Thanks
You have a space between __ and FW. Delete that.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @mahoneypat
There is a error message when delete to space
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"
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)