Forum Discussion
Cumulative Growth Factor in SSAS Tabular model
Hi Nskv,
Thanks a lot for your query, the query is perfectly working for Month and Year. for Quarter hope we need some correction, results for quarter is not correct. refer below data, for year 2018 Q1, Q2, Q3, Q4 values are blank.
I am also trying to get quarter results, once final query is ready i will reply with soultion accepted.
mean while if possible would you please help me in this regards,
my Query.
Running Total Percentage:=
VAR __dateFilterY = STARTOFYEAR('CLIENT_SERVICE_SETUP'[ACTIVATION_DATE])
VAR __dateFilterQ = STARTOFQUARTER('CLIENT_SERVICE_SETUP'[ACTIVATION_DATE])
VAR __dateFilterM = STARTOFMONTH('CLIENT_SERVICE_SETUP'[ACTIVATION_DATE])
RETURN
CALCULATE([cumulative_Customer], FILTER(ALLSELECTED('CLIENT_SERVICE_SETUP'),
SWITCH(TRUE(),
ISFILTERED('CLIENT_SERVICE_SETUP'[Year]),'CLIENT_SERVICE_SETUP'[ACTIVATION_DATE] < __dateFilterY,
ISFILTERED('CLIENT_SERVICE_SETUP'[Quarter]),'CLIENT_SERVICE_SETUP'[ACTIVATION_DATE] < __dateFilterQ,
ISFILTERED('CLIENT_SERVICE_SETUP'[Month]),'CLIENT_SERVICE_SETUP'[ACTIVATION_DATE] < __dateFilterM)))
| Year | Quarter | Month | cumulative_Customer | Running Total Percentage | Cumulative Growth Final |
| 2018 | Qtr 1 2018 | Feb-18 | 43 | ||
| 2018 | Qtr 1 2018 | Mar-18 | 90 | ||
| 2018 | Qtr 2 2018 | Apr-18 | 118 | ||
| 2018 | Qtr 2 2018 | May-18 | 158 | ||
| 2018 | Qtr 2 2018 | Jun-18 | 172 | ||
| 2018 | Qtr 3 2018 | Jul-18 | 181 | ||
| 2018 | Qtr 4 2018 | Oct-18 | 189 | ||
| 2018 | Qtr 4 2018 | Nov-18 | 192 | ||
| 2019 | Qtr 1 2019 | Jan-19 | 193 | 192 | 0.520833333 |
| 2019 | Qtr 1 2019 | Feb-19 | 195 | 192 | 1.5625 |
| 2019 | Qtr 1 2019 | Mar-19 | 198 | 192 | 3.125 |
| 2019 | Qtr 2 2019 | May-19 | 212 | 192 | 10.41666667 |
| 2019 | Qtr 2 2019 | Jun-19 | 235 | 192 | 22.39583333 |
| 2019 | Qtr 3 2019 | Jul-19 | 244 | 192 | 27.08333333 |
| 2019 | Qtr 3 2019 | Aug-19 | 271 | 192 | 41.14583333 |
| 2019 | Qtr 3 2019 | Sep-19 | 316 | 192 | 64.58333333 |
| 2019 | Qtr 4 2019 | Oct-19 | 319 | 192 | 66.14583333 |
| 2019 | Qtr 4 2019 | Nov-19 | 323 | 192 | 68.22916667 |
| 2019 | Qtr 4 2019 | Dec-19 | 333 | 192 | 73.4375 |
Regards,
SKM
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