Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
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...
Any suggestions will be greatly appreciated.
Can you use the Quartername function?
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'.
'Q'&WildMatch(Quartername(Date1),'Jan-*','Apr-*','Jul-*','Oct-*'))