I am kind of lost with this request for getting dynamic quarters in the measures. Requirement is as below :
- Have 3 date field. Date 1 Date 2 and Date 3 in data model
- Have 3 categories - A, B, C which is not in the data model so added as inline table.
- Business logic is based on quarters. So I have derived quarter based on Date 1 and Date 2
Logic - If (categories='A', count(date 1<=12/31/2019, date 2>12/31/2019) ,
If(Categories ='b', count (date2>=12/31/2019, date 3>12/31/2019,
If (Categories='c', count (date1>12/31/2019, date 3 <12/31/2019))).
So above logic is for Q1, similarly I have to do it for all the quarters present in the data. Currently I have implemented it in the measure for all quarters like Q1, Q2, Q3, Q4 and so on which is showing as 100 lines of script on the frontend. I wanted to know if there is a way I can change the quarters dynamically in the above logic. Like instead of writing above script for each quarter in 1 script if only the quarter end date changes...
Assuming that Q1 starts in Jan, the Quartername function returns 'Jan-Feb 2020', the WildMatch will return a number 1 to 4 which is concatenated to 'Q'.
