Hi All,
I am trying to build Financial Week Calendar in Power Bi but I need your help as I am not getting the correct answer. I have build calendar from 01/04/2018 to 31/03/2019.
I have created a column with Weeknumber starting on Sunday and then I have written DAX to give me
week. financial
Unfortunately the formula is not quite right as 31/03/2019 is week 1 but it should be week 53.
Please watch the following video from Sam on how to setup a Date Table. I will attach the JSON file as well that he uses in the video.
Power BI Date Table code.txt (2.8 KB)
Thanks
unfortunately this code doesn’t solve my issue as you don’t provide financial week there.
I need to set up Financial Week and I am nearly able to do it (see screen shot) but my formula needs to be adjusted somehow and I will appreciate help.
I don’t know how to adjust the formula to mate 31/03/2019 week 53 as 01/04/2019 is my week 1 .
Here is one way to do it. Will need a Fiscal Day column first, then can do a column for Fiscal Week.
On the Fiscal Day column this is for a company who’s fiscal year starts in April, so change the 4 to the appropriate month to get the correct result.
Fiscal Day =
'Calendar'[Date]
- DATE ( ( 'Calendar'[Fiscal Year] - 1 ), 4, 1 ) + 1
Fiscal Week =
ROUNDUP ( 'Calendar'[Fiscal Day] / 7, 0 )
Thanks
Looks good! Make sure you change the formatting though for Fiscal Day & Fiscal Week Columns to “Whole Number”. I believe you had Fiscal Day set as Date/Time, and other was a Decimal#. Here is updated file, and I have added a few extra columns you may want as well.
Date Table- Fiscal Year Test.pbix (115.6 KB)
Thanks
Thank you very much !
Make sure that you mark this as a solved solution, this helps us figure out which questions have been solved.
Thanks
