Tertial in Extended Date Table

Power Query M Code Showcase
1

Hi @Melissa and @Greg !
Thanks for a great calendar. Are there any plans to add “Tertial” to this calendar or do I need to fix this myself? Tertial = 4 months.

2

Hi @CATZ

Just got a question, I’ve never heard of Tertial (4 months) in my years in finance. What kind of field (business) uses this kind of reporting.

thanks
Keith

3

Hi @Keith
I’m working for one of the top (globally) Home Furnishing Retailers where we use both
quarters (Q1, Q2, Q3 & Q4) and tertial (T1, T2 & T3) in our financing and business reporting.

// CATZ

4

Hi @CATZ

Thanks for the information

Keith

5

Hi @CATZ

Please do not call-out specific forum members names directly … this is to your benefit as well, as it will increase the number of forum members who will review your issue.

Regardless, here’s a quick-and-dirty addition of trimester and semester values to the extended dates table:

eDNA Forum - Trimester and Semester - 2
eDNA Forum - Trimester and Semester - 21560×1232 164 KB

eDNA Forum - Trimester and Semester - 1
eDNA Forum - Trimester and Semester - 11920×1089 240 KB

Here’s the code block:

      // start - add Trimester
      InsertTrimesterNumber = Table.AddColumn(InsertCompletedQuarter, "Trimester Number", each 
        if Date.Month([Date]) <= 4 then 1 else if Date.Month([Date]) <= 8 then 2 else 3, Int64.Type),
      InsertTrimester = Table.AddColumn(InsertTrimesterNumber, "Trimester", each 
        "T" & Number.ToText([Trimester Number]), type text),
      // end - add Trimester

      // start - add Semester
      InsertSemesterNumber = Table.AddColumn(InsertTrimester, "Semester Number", each 
        if Date.Month([Date]) <= 6 then 1 else 2, Int64.Type),
      InsertSemester = Table.AddColumn(InsertSemesterNumber, "Semester", each 
        "H" & Number.ToText([Semester Number]), type text),      
      // end - add Semester

I’m sure there are better/more elegant ways to do this, but it works.

You’ll need to update the preceding query step name in the “InsertMonth” step as well.

I can’t speak for the future plans of the Extended Date Table.

As to your earlier question, if you’re able, I would always try to fix it before seeking help from others.

Hope this helps.
Greg
eDNA Forum - Trimester and Semester.pbix (109.2 KB)

1 Like
6

Hi !
Thanks for the fix, I will try to incorporate this into the original table. My knowledge about M is very limited, thus the question before trying to fix it. I’m new to using forums but I guess I will learn…

// CATZ