Cumulative Growth Factor in SSAS Tabular model

[Anonymous]

Hi Guys, 

I am facing new challange in Cumulative Growth Factor in SSAS Tabular model. 

below are the steps.

below is the data:

ID	Activation_Date
1777230002	2/22/18 0:00
1777230003	2/22/18 0:00
1777270029	2/27/18 0:00
1778070068	3/7/18 0:00
1778070069	3/7/18 0:00
1778060060	3/6/18 0:00
1778060065	2/22/18 0:00
1778100080	3/17/18 0:00
1778060063	2/27/18 0:00
1778110081	3/24/18 0:00
1777230001	2/22/18 0:00
1777260028	2/22/18 0:00
1778030040	3/3/18 0:00
1778040041	2/22/18 0:00
1778140098	3/14/18 0:00
1778120086	3/31/18 0:00
1778120088	3/31/18 0:00
1778130092	2/22/18 0:00
1778120084	3/10/18 0:00
1778120089	3/12/18 0:00

step 1 : Customer_Count:= count(ID)

step2 :cumulative_Customer:= CALCULATE(COUNT(CLIENT_SERVICE_SETUP[CLIENT_ID]),
FILTER(ALLSELECTED('CLIENT_SERVICE_SETUP'),CLIENT_SERVICE_SETUP[ACTIVATION_DATE]<=MAX(CLIENT_SERVICE_SETUP[ACTIVATION_DATE])))

Step 3: Cumulative_Growth:= calculate(divide(customer Count,cummulative_Customer))

below is the output data.

Month	Cumulative Growth	cumulative Customer	Customer Count
Feb-18	1	43	43
Mar-18	0.52222222	90	47
Apr-18	0.23728814	118	28
May-18	0.25316456	158	40
Jun-18	0.08139535	172	14
Jul-18	0.04972376	181	9
Oct-18	0.04232804	189	8
Nov-18	0.015625	192	3
Jan-19	0.00518135	193	1
Feb-19	0.01025641	195	2
Mar-19	0.01515152	198	3
May-19	0.06603774	212	14

 My Requirement is : (customer count/cumulative customer), the results are good. but need small change in above requirement. 

my Requirement:  Customer_Count/lag(Cumulative_Customer)

1)

may-18: customer_Count: 47

Feb-18: cummulative_Customer: 43

calculation is : 47/43.

2)

Apr-18: customer_Count: 28

Mar-18: cummulative_Customer:90

calculation : 28/90

I am not able to get : Customer_Count/lag(Cumulative_Customer) how to achieve this result.

would some one help me in this regards,

Please let me know if you need any more details.

Regards,

SKM

[Anonymous]

I believe you can use DATEADD()

I.e. growth = [Customer Count] / CALCULATE( [Customer Cummulative], DATEADD( Calendar[DateDateTime], -1, MONTH))

If this works then please mark it as the accepted solution.

[Anonymous]

Hi Nskv,

Thanks a lot for your great help!

provide DAX query works fine, in my requirement i have to drill up value(Measure) for year, Quarter and Month in Line chart visual. to get that, Dateadd function hope we can't specify month, we have to find other options to get generic date where i can drill down to year, Quarter and Month. would you please help me in this regards.

Regards,

SKM

[Anonymous]

Try this:

value cummulative (t-1) =
VAR __dateFilterM = STARTOFMONTH( 'DimCalendar'[DateDateTime])
VAR __dateFilterQ = STARTOFQUARTER( 'DimCalendar'[DateDateTime])
VAR __dateFilterY = STARTOFYEAR( 'DimCalendar'[DateDateTime])

RETURN
CALCULATE(
    [value];
    FILTER(
        ALL('DimCalendar');
        SWITCH(
            TRUE();
            ISFILTERED( 'DimCalendar'[CalendarMonthName]); 'DimCalendar'[DateDateTime] < __dateFilterM;
            ISFILTERED( 'DimCalendar'[CalendarQuarterName]); 'DimCalendar'[DateDateTime] < __dateFilterQ;
            'DimCalendar'[DateDateTime] < __dateFilterY
        )
    )
)

[Anonymous]

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

[Anonymous]

Hmm... I can see some differences between our measures, but I do not know if these differences are causing the issues.

1. 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.
2. 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!

[Anonymous]

Hi @Anonymous 

Is the issue fixed?

[Anonymous]

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

[v-alq-msft]

Hi, @Anonymous 

Based on your description, you may click 'Edit Query', go to Query Editor, choose 'Add Column' ribbon, select 'Index Column', click 'Close and Apply'.

Then you may create the measure as follows.

return CALCULATE(
         SUMX(
             ALLSELECTED('Table'),
         DIVIDE(
                CALCULATE(
                    SUM('Table'[Customer Count]),
                    'Table'[Index] = _index+1
                ),
                SUM('Table'[Cumulative Customer])
        )
))

Result:

If I misunderstand your thought, please show me your expected output. I am glad to solve the problem for you.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Allan,

Thanks a lot for your great help. your understanding is correct but DAX which you shared not able to execute in SSAS tabular cube. in my requirement i need to add 1 more point that we need to implement cumulative total on Month & Quarter

formula :

Curren_month[cummulative_customer]-previous_month[cummulative_Customer]/previous_month[cummulative_Customer]

example: 

Jan-18: cummulative_Customer: 47

Feb-18: cummulative_Customer: 43

calculation: 43-47/43

Note: we need to consider only cummulative_customer measure.

i have attached pbix file with to get more clarity.

https://www.dropbox.com/s/a92hq4dqejykhg1/Client_Details.pbix?dl=0  

would you please let us know any more details required.

Regards,

SKM