Issue with Retrieving Quarter End Date Based on Prompts

[msinha8]

Hello Gurus,

I am working on building a logic to filter records based on the Year (e.g., 2025) and Quarter (e.g., Q2) values passed as prompts.

I’ve created these prompts as Presentation Variables and am trying to retrieve the corresponding Quarter End Date (e.g., 03/31/2025 for Year = 2025 and Quarter = Q2). However, I’m encountering issues in deriving this value correctly.

Here is the calculation I’m currently using:

case(
and(
POC_Schema.Date_Test.Year = $PV_YEAR,
POC_Schema.Date_Test.Quarter_Number = $PV_QTR
),
POC_Schema.Date_Test.Quarter_End_date,
date('1990-01-01')
)

Note: Date_Test is my calendar table, and my fact table contains data as of today.
Any suggestions or guidance on how to correctly extract the Quarter End Date using the presentation variables would be greatly appreciated.

Thank you in advance!

[Venkat · Accepted solution]

Hi,

Steps)
1. Please Convert the Year and Quarter values to a date format as outlined below.

To accomplish this, the date function necessitates three columns: Year, Month, and Day.
Convert the Quarter value to the starting month of the quarter. Specifically, Q1 should be converted to 1, Q2 to 4, Q3 to 7, and Q4 to 10. This conversion can be achieved using a case statement in conjunction with the subString function.

date(
concat(
string(
$PV_YEAR
),
'-',
case(
substring(
string(
$PV_QTR
),
1
) = 1,
'1',
substring(
string(
$PV_QTR
),
1
) = 2,
'4',
substring(
string(
$PV_QTR
),
1
) = 3,
'7',
'10'
),
'-01'
)
)

2.Once the date column has been created, please apply the quarterEndDate formula.

quarterEndDate(
date(
concat(
string(
$PV_YEAR
),
'-',
case(
substring(
string(
$PV_QTR
),
1
) = 1,
'1',
substring(
string(
$PV_QTR
),
1
) = 2,
'4',
substring(
string(
$PV_QTR
),
1
) = 3,
'7',
'10'
),
'-01'
)
)
)

Thanks 

Venkat

[msinha8]

@Venkat 

Thanks for the solution, it is working as expected.