Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Cumulative Growth Factor in SSAS Tabular model

Hi Guys, 

 

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

below are the steps.

below is the data:

IDActivation_Date
17772300022/22/18 0:00
17772300032/22/18 0:00
17772700292/27/18 0:00
17780700683/7/18 0:00
17780700693/7/18 0:00
17780600603/6/18 0:00
17780600652/22/18 0:00
17781000803/17/18 0:00
17780600632/27/18 0:00
17781100813/24/18 0:00
17772300012/22/18 0:00
17772600282/22/18 0:00
17780300403/3/18 0:00
17780400412/22/18 0:00
17781400983/14/18 0:00
17781200863/31/18 0:00
17781200883/31/18 0:00
17781300922/22/18 0:00
17781200843/10/18 0:00
17781200893/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.

MonthCumulative Growthcumulative CustomerCustomer Count
Feb-1814343
Mar-180.522222229047
Apr-180.2372881411828
May-180.2531645615840
Jun-180.0813953517214
Jul-180.049723761819
Oct-180.042328041898
Nov-180.0156251923
Jan-190.005181351931
Feb-190.010256411952
Mar-190.015151521983
May-190.0660377421214

 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

 
Need Help

9 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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
        )
    )
)