How to calculate month end dates from all months?

Question

Hi,

I am getting last day of current month. That formulae is

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

But, I want month end dates only from all months.i need  formula for this.

Please help me.

Thanks & Regards,

A.kavya.

SriniVEERAVALLI · Answer

create min(date),max(date) fields in the rpd and set content level for Month level

or try

start->TIMESTAMPADD(SQL_TSI_DAY,1,min("Time"."Date" by "Time"."Month Name"))

last->TIMESTAMPADD(SQL_TSI_DAY,1,max("Time"."Date" by "Time"."Month Name"))

in report.

Sandeep Andhale · Answer

Hello Kavya,

in your expression contain the current date instead of that ,

create new date column it must contain every month date ,

created date column pass  input to your expression .

new created column create as(New Calculated measure or New calculated Item or new Group )

regards

Sandeep

Gianni Ceresa · Answer

The REPEAT() function? this one will just output the first param as many times as the second one, so how is it supposed to give you end of month for all the months?

REPEAT('abc', 4) => 'abcabcabcabc' (so 'abc' 4 times)

And CASE WHEN will never generate any rows of data, if there aren't dates somewhere in the dataset it will never generate new columns with data inside.