Custom date column using quarters

[Anonymous]

I am trying to create a custom column which will display "Past" , "Present", and "Future" based on criteria below. 

Current quarter today (7-19-22) is Q3

Past = before last Quarter  ( Q1 22, Q4 21, Q3 21... Q1 18 etc. )

Present = Last Quarter plust next 4  (Q2 22 until Q2 23) 

Future = After Next 4 Quarters    (Q3 23, Q4 23, Q1 24 ... Etc)

I was able to get something similar using days in a custom column.  The issue is that I need to write the same formula using the current quarter rather than the current day.  I do not want the data to change daily but rather quarter.  I cannot use this same code it seems becuase duration only works for days, hours, etc not months or quarters. 

Example code using days: 

= if Duration.Days(DateTime.LocalNow() - DateTime.From([Date])) > 90 then "Past" else if Duration.Days(DateTime.LocalNow() - DateTime.From([Date])) <= 90 and Duration.Days(DateTime.LocalNow() - DateTime.From([Date])) >=-365 then "Present" else "Future"

Any ideas how I could get similar results based on quarters rather than duration days? 

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can add a custom column with the following code

if [Date] < Date.AddQuarters(Date.StartOfQuarter(Date.From(DateTime.LocalNow())),-1) then "Past" else if [Date] < Date.AddQuarters(Date.StartOfQuarter(Date.From(DateTime.LocalNow())),4) then "Present" else "Future"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Knighthawk]

Hi scottjw828,

I am relatively new to Power Query, so I'm sure there might be a more effecient way to do this that what I am providing, but I came up with something based on my current level of knowledge of M code.  Hopefully this will work for you:

Note:  I added the "TODAY" in as a tracker into a column that I named "Sales".   You should be able to adjust the "Sales" column to some other column that matches your source data, then the second to last step filters out "TODAY" from the data.  Also, you may need to work adjust the #"Changed Type" step, or add a type to the "Current" step depending on what your source data looks like since the date coming in from the "Current" step is naturally a date type.

let
    Current = Table.FromRecords({[Date = Date.From(DateTime.LocalNow()), Sales = "TODAY"]}),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Combine = Table.Combine({Current, Source}),
    #"Changed Type" = Table.TransformColumnTypes(Combine,{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Inserted End of Quarter" = Table.AddColumn(#"Sorted Rows", "End of Quarter", each Date.EndOfQuarter([Date]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted End of Quarter", {"End of Quarter"}, {{"Count", each _, type table}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Index", "Count", {"Date", "Sales"}, {"Date", "Sales"}),
    Index = #"Expanded Count"{11}[Index],
    Custom1 = Table.AddColumn(#"Expanded Count", "Period", each if [Index] < Index -1 then "Past" else if [Index] > Index +4 then "Future" else "Present"),
    #"Filtered Rows" = Table.SelectRows(Custom1, each ([Sales] <> "TODAY")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "End of Quarter"})
in
    #"Removed Columns"

Here is a walkthrough of what this code is essentially intended to do:

-  adds into your source data "todays date" along with a "TODAY" tracker into the "Sales" column (adjust this "Sales" column name to a column that you will match to a column within your source data)

-  sorts the dates in asceding order

-  adds the end of quarter date for each date

-  groups by quarter

-  ranks the groupings

-  expands the data back out

-  adds in a column that performs the logic for "Past", "Present", and "Future"

-  filters out the "today's date" row, but filtering out "TODAY" from the "Sales" column (adjust "Sales" to your matching source data column)

- removes the End of Quarter column used for the grouping and ranking earlier in the code

If you run into any trouble or have any questions, please feel free to reach back out with any questions.