Fiscal Period Offset in Power Query

BillK · July 9, 2020, 11:29pm

My company uses a variation of 4-4-5 calendar. We refer to them as “Periods” instead of months. For example we have P1 to P12 with a P13 for year-end adjustments.

I want to create a Period offset column in my calendar table.

For a Day Offset, I normally just put in a “Today” column with the current date (DateTime.LocalNow()) and then calculate the offset with the formula [Date]-[Today].

However, I’m not sure how to create the offset for the periods.

Below is an example

In the example P1 ends on 1/26. The current day is 1/28. You can see how the Period offset should be calculated, but I don’t know how to do it in M-Code.

I’m hoping if I get this to work, I can build a nice Calendar table that I can reuse in all my models that will have the time offsets built in.

Thanks,

EnterpriseDNA · July 10, 2020, 12:38am

Hi @BillK, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

Melissa · July 10, 2020, 6:13am

Hi @BillK,

Can you share the full M code for the Calendar table you’ve created thus far in a PBIX file ?
I’ll be glad to take a look

Thanks!

BillK · July 10, 2020, 1:36pm

Hi Melissa,

Period Offset Model.pbix (10.9 KB)

I’ve uploaded the queries that make up my Calendar table.

My thought was if I could create a column for [Today’s Year-Period Index] then I could create the offset by creating a column [Year-Period Index] - [Today’s Year-Period Index].

But I don’t know how to do the lookup to create the [Today’s Year-Period Index] column

Thank you for taking the time to look at this.

Melissa · July 10, 2020, 1:39pm

Can you check your file please @BillK ?
its empty…

BillK · July 10, 2020, 1:42pm

Ooops, Try this one.
Period Offset Model.pbix (37.1 KB)

Melissa · July 10, 2020, 2:32pm

Okay so you didn’t provide the accompanying data, I made an export for that…

Loaded that into a new Query called tCalendar and created a FileLocation Parameter for that so save the xlsx and copy the full filepath+filename into that parameter and you’re good to go.

Here’s what I dd to create the custom offsets for you:

CurrentDate is to determine Today
IdentifyCurrentDate filters down your query to the record matching the CurrentDate
CurrentPeriod extracts the value from that record for the Year-Period
BufferTable loaded a one column table with all distinct Year-Periods into memory
InsertPeriodOffset counted the number of rows in that BufferTable after filtering it down

.

let
    Source = Excel.Workbook(File.Contents(FileLocation), null, true),
    tCalendar_Table = Source{[Item="tCalendar",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(tCalendar_Table,{{"Date Period Key", type text}, {"Date", type date}, {"Period", Int64.Type}, {"FMnth", type text}, {"Year", Int64.Type}, {"P Begin Date", type date}, {"P End Date", type date}, {"Today", type date}, {"Day Offset", Int64.Type}, {"Year-Period", Int64.Type}, {"Year-Period Index", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Today", "Day Offset"}),

    // Added steps here
    CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
    IdentifyCurrentDate = Table.SelectRows(#"Removed Columns", each ([Date] = CurrentDate)),
    CurrentPeriod = IdentifyCurrentDate{0}[#"Year-Period"],
    BufferTable = Table.Buffer(Table.Distinct( #"Removed Columns"[[#"Year-Period"]])),
    InsertPeriodOffset = Table.AddColumn(#"Removed Columns", "Period Offset", each 
        if _[#"Year-Period"] > CurrentPeriod  = false then -Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[#"Year-Period"] > _[#"Year-Period"] and IT[#"Year-Period"] < CurrentPeriod))
        else if _[#"Year-Period"] = CurrentPeriod  then 0
        else Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[#"Year-Period"] < _[#"Year-Period"]  and IT[#"Year-Period"] > CurrentPeriod )), type number)
in
    InsertPeriodOffset

.
With this result.

.
I hope this is helpful. Here are the files.
Period Offset Data.xlsx (50.9 KB)
Period Offset Model.pbix (50.4 KB)

BillK · July 10, 2020, 4:15pm

Melissa,
This is beautiful.
Thank you so much. I definitely wasn’t going to stumble on to this solution by myself. The great part is I even think that I understand your code and what you’re doing. I should be able to adapt this technique if I run into a similar situation in the future (looking up a field value in a specific row).
Awesome!

BillK · July 12, 2020, 1:59pm

Melissa,

I stitched your code into my calendar table and it works great. Thank you so much! As I put in the code, I realized that I didn’t understand it quite as well as I thought I did.

To help with my understanding, can you explain what is happening with this part of the code…
Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[#“Year-Period”] > _[#“Year-Period”] and IT[#“Year-Period”] < CurrentPeriod))

I guess I don’t really understand how the Table.SelectRows function is working here. I also don’t get the (IT) portion.

Regards,

Bill

Melissa · July 12, 2020, 2:51pm

No worries @BillK

Let’s examine this part of the code a little closer:

CurrentPeriod = IdentifyCurrentDate{0}[#"Year-Period"],
BufferTable = Table.Buffer(Table.Distinct( #"Removed Columns"[[#"Year-Period"]])),
InsertPeriodOffset = Table.AddColumn(#"Removed Columns", "Period Offset", each 
        if _[#"Year-Period"] > CurrentPeriod  = false then -Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[#"Year-Period"] > _[#"Year-Period"] and IT[#"Year-Period"] < CurrentPeriod))
        else if _[#"Year-Period"] = CurrentPeriod  then 0
        else Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[#"Year-Period"] < _[#"Year-Period"]  and IT[#"Year-Period"] > CurrentPeriod )), type number)

In the CurrentPeriod variable we’ve stored the value for the #“Year-Period” from the record that is equal to todays date.

in the BufferTable variable we’ve extracted a single column from the output generated by the Previous step in the Query and then removed all duplicates values thus keeping only distinct [#“Year-Period”] values

Next inside the InsertPeriodOffset variable we use an if statement to determine:

if the value for the column [#“Year-Period”] in the current record > CurrentPeriod = false
If this evaluates to TRUE using Table.SelectRows we filter down the BufferTable with a custom function and reference the BufferTable (= the Inner Table) by assigning it the variable name IT
So for each row in the date table where the result for the if statement returns TRUE you can read the filter logic that follows as: BufferedTable[#“Year-Period”] > CurrentRow[#“Year-Period”] and BufferedTable[#“Year-Period”] < Todays[#“Year-Period”]
The Offset value is now equal to the number of rows left in the filtered BufferedTable multiplied by -1
if if the value for the column [#“Year-Period”] in the current record = CurrentPeriod
then 0 because the current row value is equal to Todays[#“Year-Period”] value
here we do pretty much the reverse of (1) and we don’t multiply by -1 because these are future dates so we want the numbers to remain positive.

.

I hope this is helpful.

BillK · July 12, 2020, 9:33pm

Melissa,

That was very helpful.

I know the formula works, but when I tried to manually replicate in excel everything seemed off by 1.

For example
Today’s Current Period = 201904
Current Row being Evaluated = 201903

When evaluating BufferedTable>Current Row, you should get True for BufferedTable starting at 201904 and increasing.
When evaluating BufferedTable<Today’s Current Period, you should get True for BufferedTable starting at 201903 and decreasing.
There are no rows where both are true, so the countrows result is 0

I’m attaching my little excel sheet that tried to work out the logic.

Where am I making the mistake?

I really appreciate you taking the time to talk me through the problem.

Here is my excel file
Evaluate Fiscal Period Offset.xlsx (11.1 KB)

Melissa · July 12, 2020, 10:06pm

Hi @BillK,

Sorry but I’m nowhere near my computer at the moment but I’ll offer another approach that will hopefully help you get a better understanding of what is happening in the M code.
Open the Advanced Editor in Power Query and change the InsertPeriodOffset step as follows.

 InsertPeriodOffset = Table.AddColumn(#"Removed Columns", "Period Offset", each 
            if _[#"Year-Period"] > CurrentPeriod  = false then Table.SelectRows( BufferTable, (IT) => IT[#"Year-Period"] > _[#"Year-Period"] and IT[#"Year-Period"] < CurrentPeriod)
            else if _[#"Year-Period"] = CurrentPeriod  then 0
            else  Table.SelectRows( BufferTable, (IT) => IT[#"Year-Period"] < _[#"Year-Period"]  and IT[#"Year-Period"] > CurrentPeriod ), type table)

Now when you return to the query, you’ll see a nested table object inside the table, except for the current period records and when you click of to the side, so not on the nested table object itself, you’ll get a preview down below in the preview pane of that nested table. Allowing you to see the results of the filter logic.

I hope this is helpful.