Creating Custom Date Table

Hi all,

I’ve used 445 calendar before for my master dates table. But, the financial year starts before the actual year ends.

My question is, how do we create 445 calendar with January 1st always belongs to week1, regardless of which day it is starting?

My criteria is,

  1. All weeks start on Saturdays.
  2. January 1st always belongs to week1. This is the reference date.

I appreciate any help. Thanks!

What sort of custom date table do you have right now?

It seams you just require a calculated column for weeks?

I’m honestly not sure of the answer right now, as probably need to see more around the scenario rather than having to start from scratch on this.

Thanks
Sam

I have a 445 calendar right now. But, in that the financial year actually ends on Dec 30th and Dec 31st is considering as next financial year start date.

In my case, I don’t want that to happen. My calendar should follow 445 formula but, every financial year should start on Jan 1st and end on Dec 31st.

I know because of this, we may have less days in Q1 and more days in Q4. I just need to make sure having correct Quarter, Month and Week Id’s.

I have attached an image showing my calendar. Please have a look at it if the above explanation is not clear.

Thanks!!

Ok sure thing.

I think for assistance here probably need to see the example date table inside of Power BI.

Have you attempted to create the column with some formula?

It shouldn’t be too difficult. All you would need to do is use the right formula and the date column to work this out.

Some ideas here

Also maybe something like this…

Fiscal Year = IF(AND([Year] =2012,[Month]<=12),"FY13",
IF(AND([Year] =2013,[Month]<=6),"FY13",
IF(AND([Year] =2013,[Month]<=12),"FY14",        
IF(AND([Year] =2014,[Month]<=6),"FY14",
IF(AND([Year] =2014,[Month]<=12),"FY15",
IF(AND([Year] =2015,[Month]<=6),"FY15",
IF(AND([Year] =2015,[Month]<=12),"FY16",
IF(AND([Year] =2016,[Month]<=6),"FY16",
IF(AND([Year] =2016,[Month]<=12),"FY17",
IF(AND([Year] =2017,[Month]<=6),"FY17",
IF(AND([Year] =2017,[Month]<=12),"FY18",
" ")))))))))))

See how you go.

Thanks
Sam