Power Query - Calendar Table

[Nilawari]

Good morning - I have a fiscal calendar that my company creates which I pull into Power BI. Columns include date, fiscal year, fiscal week, day of week, work day, holiday, holiday name, week of month, and fiscal quarter.

What I want to do is creat a few custom column in power query that will identify the current fiscal year, fiscal quarter, fiscal month, and fiscal week with a zero and for past it counts negative and future it counts positive. I have seen examples of this but no details and I haven’t been able to figure this out. Can anyone help me out on this one?

 

[macfuller]

You're asking for quite a few options here, but essentially it seems you need to set a starting point based on the date that you load the calendar and work backward.

If I'm understanding your request, first you'll need to set the starting point. For example, the code below will give you a day of the week based on the first date of your range.

MinDate = Date.FromText("20190101")   // Arbitrary date
// or
MinDate = DateTime.Date(DateTime.FixedLocalNow())  // current date

then you can do

StartFiscalWeekDate = Date.AddDays(MinDate, -(Date.DayOfWeek(MinDate) + 1) )

To count the weeks ahead and behind from today's date you can use

InsertedWeeksAway = Table.AddColumn(InsertedWorkDay, "Weeks Ago", each Number.RoundDown(Number.From((DateTime.Date(DateTime.FixedLocalNow())-[Date])/7))+1, type number)

If you want to adjust your weeks based on what you came up with for StartFiscalWeekDate you can put that in instead. Figure out the math for a StartFiscalMonthDate, StartFiscalQuarterDate, and so on.

You'll have to adjust your numbers for the month, quarter, etc. For your prior quarter would you just count back <= 90 days from your starting point, or are you tying it to the fiscal values? If you're tying into the calendar (e.g. you're 78 days into the 2nd fiscal quarter so -79 days puts you into the prior quarter rather than -90 days) then you'll probably want helper columns to make the math easier.

I hope this gets you started. I've always hated date arithmetic!

 

[Nilawari]

Thank you macfuller. I was able to insert the first two bits of code but I am running into a snag with the third bit that counts the ahead and behind. I keep getting an error and unclear how/where I add that code.

 

[sandy666]

look at https://www.mrexcel.com/forum/power...get-fiscal-year-fiscal-month.html#post5226438 or https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/

 

[macfuller]

Perhaps because I have "InsertedWorkDay" as my previous step which was from my code? You should replace that, if you haven't already, with the name of your previous step.

 

[Nilawari]

Thank you all. I was able to finally get what I needed using all your suggestions. I decided to build the calendar from scratch using the links and the code really helped me get "unstuck". In the end things started to click and I was able to complete what I wanted.

 

[sandy666]

Glad to help

Have a nice day