Date Based Dimension

[rakeshkumar1890]

Hello Folks,

I have date and need below requirement as Bucket dimension. This requirement should be in script.

Here the financial Year would be : Nov to Jan, Feb to Apr, May to Jul and Aug to Oct

We are in Current Month : Nov

Note : If we are in Nov month then bucket should be Current Month and balance would be Current Quarter which are two month( Dec and Jan) as below

Date	Bucket
2022-11-19	Past Due
2023-01-10	Past Due
2023-09-14	Past Due
2023-10-18	Past Due
2023-10-19	Past Due
2023-11-01	Current Month
2023-11-02	Current Month
2023-11-29	Current Month
2023-11-30	Current Month
2023-12-01	Current Quarter
2024-01-13	Current Quarter
2024-01-14	Current Quarter
2024-02-26	Outside of Quarter
2024-02-29	Outside of Quarter
2024-03-01	Outside of Quarter
2024-04-03	Outside of Quarter
2024-07-15	Outside of Quarter
2025-01-01	Outside of Quarter
2026-11-01	Outside of Quarter

Thanks in advance

[Or]

If(Date<Today(),'Past Due',if(Monthname(Date) = Monthname(Today()),'Current Month',if(Quartername(Today(),0,11) = Quartername(Date,0,11),'Current Quarter','Outside of Quarter')))

(I can't syntax-check this at the moment so the parenthesis may need to be fixed)

[rakeshkumar1890 · Accepted solution]

Thanks @Or  for your quick response 

There is one change and working fine.

If(Monthname(Date) < Monthname(Today()),'Past Due',
if(Monthname(Date) = Monthname(Today()),'Current Month',
if(Quartername(Today(),0,11) = Quartername(Date,0,11),'Current Quarter','Outside of Quarter')))