I am trying to create a calculated field which shows previous months value according to the date range selected in a filter.
For eg: If user selects start date as March 1st and End Date as March 13th, the calculated field will show the value for whole Feb month.
I have created 3-4 different fields but realised that when the date filter is applied. The dataset gets filtered and I don’t have the data of last month so the calculation always gives null.
I am not sure how to resolve this issue.
Any help is appreciated.
Below is a sample calculation which works without filter but in real use case the filter will be applied on table and we will be showing the comparison for previous period.
countIf({cs_transaction_id}, dateDiff({cs_create_time},endDate,‘MM’)= 1 AND dateDiff({cs_create_time},endDate,‘YYYY’)= 0 AND {cs_response_code}=‘0’)/countIf({cs_transaction_id}, dateDiff({cs_create_time},endDate,‘MM’)=1 AND dateDiff({cs_create_time},endDate,‘YYYY’)= 0)
cs_create_time is a datetime column. endDate is the end date of the date range used in filter.