Forum Discussion
Cumulative Growth Factor in SSAS Tabular model
Hmm... I can see some differences between our measures, but I do not know if these differences are causing the issues.
- I have a date-table, which I believe you should implement as well. If you already have it then you should be referencing the date table where you have written ACTIVATION_DATE.
- Switch checks from the top, thus in your query it checks if [Year] is filtered, and if this is true then it do not care if you have filtered by quarter or month as well. I would therefore start by month then quarter and then year - this should then work when you use drill down (i.e. in the matrix not the table).
Hope this helps!
Hi Anonymous
Is the issue fixed?
Hi Nskv,
As suggeted by you, i have taken new Date dim table and extracted new columns with year, Quarter & Month. in Power Bi i have choosen Line Chart, in Axis we placed year, Quarter & Months and in Value below query measure. when we drill down, Month is working fine and quarter is not working as expected, i am still working on this.
if possbile would you please suggest some other option.
Running Total Percentage:=
VAR __dateFilterM = STARTOFMONTH('DIM_CALENDER_DATE'[date])
VAR __dateFilterQ = STARTOFQUARTER('DIM_CALENDER_DATE'[date])
VAR __dateFilterY = STARTOFYEAR('DIM_CALENDER_DATE'[date])
RETURN
CALCULATE([cumulative_Customer], FILTER(ALLSELECTED('DIM_CALENDER_DATE'),
SWITCH(TRUE(),
ISFILTERED('DIM_CLIENT_SERVICE_SETUP'[Month]),'DIM_CALENDER_DATE'[date] < __dateFilterM,
ISFILTERED('DIM_CLIENT_SERVICE_SETUP'[Quarter]),'DIM_CALENDER_DATE'[date] < __dateFilterQ,
ISFILTERED('DIM_CLIENT_SERVICE_SETUP'[Year]),'DIM_CALENDER_DATE'[date] < __dateFilterY)))Regards,
SKM