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

Active Member
Joined
Apr 30, 2014
Messages
251
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

Active Member
Joined
Apr 30, 2014
Messages
251
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,082,323
Messages
5,364,589
Members
400,808
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top