Adding YearMonth and QuarterMonth into same listbox as filter

[Anonymous]

Hi All,

We have one ask where,  user wants to see payperiod filter as combination of yearmonth and yearquarter

ex.

PayPeriod

202101

202102
202103

2021Q1

202104

202105

202106

2021Q2

202103 and so on,

and when user select '2021Q1' then he should see data for '202103' same for '2021Q2' he should see for 202106 which means whenever yearquarter get selected then, always show data for last yearmonth of that respective quarter and 

when user select yearmonth then it should show data for that respective yearmonth only.

ex. '202101' should show data for '202101' and '202103' should show '202103' .

Any suggestions would be helpful.

TIA!

[Or]

Periods:

Load OriginalDate, Monthname(OriginalDate) as PayPeriod

Resident YourTable;

CONCATENATE

Load OriginalDate, Quartername(OriginalDate) as PayPeriod

Resident YourTable;

By Loading both sets of values, alongside the original dates, into a separate table, you can filter using the same field without having to duplicate or modify the original data.

[Vegar]

I'm making some adjustment to the solution by @Or  to better fit your desired output as you described it.

Periods:

Load distinct OriginalDate, Monthname(OriginalDate) as PayPeriod

Resident YourTable;

CONCATENATE (Periods)

Load

OriginalDate, Quartername(OriginalDate) as PayPeriod

Resident Periods

Where PayPeriod = monthname(quarterend(OriginalDate));