Power Query - Calendar Table

Nilawari

New Member
Joined
Apr 3, 2015
Messages
13
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

Board Regular
Joined
Apr 30, 2014
Messages
247
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.

Code:
MinDate = Date.FromText("20190101")   // Arbitrary date
// or
MinDate = DateTime.Date(DateTime.FixedLocalNow())  // current date
then you can do

Code:
StartFiscalWeekDate = Date.AddDays(MinDate, -(Date.DayOfWeek(MinDate) + 1) )
To count the weeks ahead and behind from today's date you can use

Code:
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

New Member
Joined
Apr 3, 2015
Messages
13
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.
 

macfuller

Board Regular
Joined
Apr 30, 2014
Messages
247
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

New Member
Joined
Apr 3, 2015
Messages
13
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.
 

Forum statistics

Threads
1,078,466
Messages
5,340,485
Members
399,378
Latest member
voodoo1

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top