Filter column - last month of previous quarter

Hello community,

I have a dataset where column headers are dates (one column per month).
Based on the column names - I want filter the column which is the last month of the previous quarter.

I managed to write a formula to derive the last month of the previous quarter using dynamic select - added in the workflow attached for your reference.
The formula uses DatetTmeNow function. It worked perfectly in June (it showed March as last month) but now in July its showing the June as the last month of previous quarter - which is technically correct but not what I want.

I want to filter the last month of previous quarter using the data (i.e. column headers) that I have and not the DateTimeNow function.

Please see workflow attached.

Find_last_month_of_last_quarter.yxmd

Transformation

Accepted answers

binu_acs

@amadra one way of doing this

Find_last_month_of_last_quarter.yxmd

All comments

philipmannering

Hi @amadra

This was something of a challenge....

Perhaps this works,

startswith([name], tostring(
if datetimemonth(datetimetoday()) < 4 then datetimeyear(datetimetoday()) - 1 else datetimeyear(datetimetoday()) endif    // year
) + '-' +
padleft(
tostring(
(floor(mod(tonumber(datetimemonth(datetimeadd(datetimetoday(), -4, 'month'))), 12) / 3) + 1)                             // previous quarter
* 3 // last month
), 2, '0'))

last-month-previous-quarter.yxmd

amadra

Hi @PhilipMannering - The calculation you did using DateTimeToday() does the same thing that I do using DateTimeNow(); and gives "2020-06-30" as the last month of the previous quarter.

I am looking for a way to filter the column based on the data available (i.e. column headers) and not using DateTimeToday/DateTimeNow.
For example - if the data set is from January to June, regardless of the month we are currently in - it should give "March" as the last month of previous quarter.
Example2 - if the data set is from January to September, it should filter "June" as the last month of previous quarter.
Example3 - if the data set is from January to November, it should filter "September" as the last month of previous quarter.

Please see the desired output in the workflow for reference.

binu_acs

@amadra

soccertil1108

Transpose the data to get the columns as fields. Find the Max value of the dates. [Perform the calculation @PhilipMannering provided]. Append the required date onto the records. Filter out the required field. Cross Tab back again and perform further work.

selectColumn.yxmd

binu_acs

@amadra one way of doing this

Find_last_month_of_last_quarter.yxmd

amadra

@binu_acs - brilliant! Your solution works for most of the months, except the first quarter (where we only have three or less months in the data set) - where it gives blank. I want the last month in the data set for the first quarter but I figured it out.

Thanks for taking the time to solve this! 😄

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703