Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX to calculate the growth in number of customer.

i have a table customer and it has the compant name and the created date , from this i have calsulated for eact year , each quarter and each month how many new customers have been added. 

Count of customer Added = COUNT(Customer[COMPANY_ID])
Now i need to plot a trend on the growth of the customers getting added on yearly quarterly and monthly basis 
 
for this i need to compare the current month count to previous month count and calculate growth , this is working when i am looking into just one year but when multiple year is invlved i am getting worng value:
Output expected for year: When previous value is 0 growth should be 100% 
YearCountPrevious Year countGrowth
201710 100%
20182510150%
2019372548%
2020423713%

In quarter looking for below: when we go to new year quarter previous year last quarter should be considered.

QuarterCountPrevious QuarterGrowth
Qtr4 201810 100%
Qtr1 20192510150%
Qtr 2 2019372548%
Qtr 3 2019423713%

used this formula but not able to accomdate previous year Quarter data:

prev Quarter = CALCULATE(COUNT(Customer[COMPANY_ID]),
FILTER(ALL('Customer'),
'Customer'[Quarter.number] = SELECTEDVALUE('Customer'[Quarter.number])-1))
1 ACCEPTED SOLUTION

@Anonymous 

There is my work based on your sample. For monthly growth, you can use the similar method 
"VAR currentmonth = [CREATED].[Month]". 


PBIX if needed:
https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-pazhen_microsoft_com/ERZQA7Q73yNFpls5Z1RzyCgBtgu_IkexC-OSsa0tG_WHlQ?e=GQtJkz
 

 

Qtr growth =
VAR currentyear = Customer[CREATED].[Year]
VAR currentqtr = [CREATED].[Quarter]
VAR currentqtrcou =
    CALCULATE (
        COUNT ( Customer[COMPANY_ID] ),
        FILTER ( Customer, Customer[CREATED].[Quarter] = currentqtr ),
        Customer[CREATED].[Year] = currentyear
    )
VAR previousqtr =
    CALCULATE (
        COUNT ( Customer[COMPANY_ID] ),
        ALL ( Customer ),
        PREVIOUSQUARTER ( Customer[CREATED].[Date] )
    )
RETURN
    IF (
        previousqtr = BLANK (),
        1,
        DIVIDE ( currentqtrcou - previousqtr, previousqtr )
    )

 

 

Yearly growth =
VAR _currentyear = [CREATED].[Year]
VAR _currentyearcount =
    CALCULATE (
        COUNT ( Customer[COMPANY_ID] ),
        FILTER ( Customer, Customer[CREATED].[Year] = _currentyear )
    )
VAR precount =
    CALCULATE (
        COUNT ( Customer[COMPANY_ID] ),
        ALL ( Customer ),
        PREVIOUSYEAR ( Customer[CREATED].[Date] )
    )
RETURN
    IF ( precount = BLANK (), 1, DIVIDE ( _currentyearcount - precount, precount ) )

 

 shot growth.JPG

 

Paul

 

View solution in original post

9 REPLIES 9
V-pazhen-msft
Community Support
Community Support

1. For yearly growth I added an if function to the return.

 

Year Growth = 
var currentyear = [Date].[Year]
var prevyearcount= CALCULATE(MAX([Count]),FILTER('Table1',[Date].[Year]=currentyear-1))
return IF(prevyearcount=BLANK(),1,DIVIDE([Count]-prevyearcount,prevyearcount))

 

Year.JPG

2. For quarterly growth you would need create an index columns, otherwise you cannot lookup the value from last quarter from year 2018

 

quarter Growth = 
var currentindex=[Index]
var prevcount= CALCULATE(MAX([Count]),FILTER(Table2,Table2[Index] = currentindex-1))
return IF(prevcount=BLANK(),1,DIVIDE([Count]-prevcount,prevcount))

 

 Qtr.JPG

 

Pbix if needed: https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/Ec1Owq2gyvRJqTSSM_5c...

Paul
Best

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

Anonymous
Not applicable

Hey @V-pazhen-msft  Thanks so much for this but this isnt helping me.

 

My table has multiple date columns and comapny name with multiple counts and some how this isnt helping me. here is the data example. From this i need to get a trend of the new companies added yearly, quarterly monthly basis

 

Thanks in advance !!

 

NAMECREATEDCOMPANY_ID
ddd10/17/2018 23:23440
B10/17/2018 23:45500
C10/17/2018 23:56540
D10/17/2018 23:29441
E10/18/2018 0:02560
F10/18/2018 0:020
GH10/18/2018 0:022
B10/17/2018 19:47420
A10/17/2018 19:52421
B10/17/2018 23:35460
A10/17/2018 19:59422
B10/17/2018 23:51520
hj10/17/2018 19:18400
li10/17/2018 23:40480
hhgg11/28/2018 16:22680
vft12/7/2018 11:48920
ntt1/8/2019 12:291120
c11/30/2019 15:471280
jwhe1/8/2019 12:501161
One1/17/2019 21:521260
AAA2/11/2019 15:141300
P12/12/2019 10:271320
bb2/15/2019 16:171440
TEST CUSTOMER2/20/2019 13:501520
led3/28/2019 15:331820
kdmncdi4/10/2019 10:511940
demo4/26/2019 12:452060
Cash7/4/2019 15:022520
majrj7/9/2019 11:312560
mjho8/1/2019 13:022760
kvr8/23/2019 11:172860
riijv5/18/2019 16:322200
msfjfmam5/25/2019 16:002360
ahvu8/26/2019 23:362880
cc8/27/2019 12:432901
arih7/26/2019 22:242704
akeur8/27/2019 14:012902
skruh8/27/2019 14:162903
sjrh9/4/2019 11:222920
nsrh9/30/2019 19:032980
jsruhg10/19/2019 3:333260
fjhvjsr10/4/2019 13:133060
ruhf10/21/2019 15:173280
ruhf11/28/2018 13:36600
ar11/28/2018 14:17621
ori11/28/2018 14:48640
sro12/3/2018 14:37760
srrr12/3/2018 16:53780
ropri12/4/2018 10:52801
aruhv12/4/2018 15:22820
ruhv1/7/2019 16:561080
bbb2/13/2019 10:261380
cpty72/15/2019 12:311400
NCP3/1/2019 17:551640
rif2/19/2019 17:171500
rifur3/8/2019 15:191660
lro3/14/2019 17:491680
rijfrjg3/14/2019 18:181681
oyp3/15/2019 16:241700
qwe3/19/2019 12:271720
fvhv3/20/2019 15:071740
fvfvvv3/20/2019 16:581760
api3/29/2019 18:301840
duser4/1/2019 15:171860

@Anonymous 

There is my work based on your sample. For monthly growth, you can use the similar method 
"VAR currentmonth = [CREATED].[Month]". 


PBIX if needed:
https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-pazhen_microsoft_com/ERZQA7Q73yNFpls5Z1RzyCgBtgu_IkexC-OSsa0tG_WHlQ?e=GQtJkz
 

 

Qtr growth =
VAR currentyear = Customer[CREATED].[Year]
VAR currentqtr = [CREATED].[Quarter]
VAR currentqtrcou =
    CALCULATE (
        COUNT ( Customer[COMPANY_ID] ),
        FILTER ( Customer, Customer[CREATED].[Quarter] = currentqtr ),
        Customer[CREATED].[Year] = currentyear
    )
VAR previousqtr =
    CALCULATE (
        COUNT ( Customer[COMPANY_ID] ),
        ALL ( Customer ),
        PREVIOUSQUARTER ( Customer[CREATED].[Date] )
    )
RETURN
    IF (
        previousqtr = BLANK (),
        1,
        DIVIDE ( currentqtrcou - previousqtr, previousqtr )
    )

 

 

Yearly growth =
VAR _currentyear = [CREATED].[Year]
VAR _currentyearcount =
    CALCULATE (
        COUNT ( Customer[COMPANY_ID] ),
        FILTER ( Customer, Customer[CREATED].[Year] = _currentyear )
    )
VAR precount =
    CALCULATE (
        COUNT ( Customer[COMPANY_ID] ),
        ALL ( Customer ),
        PREVIOUSYEAR ( Customer[CREATED].[Date] )
    )
RETURN
    IF ( precount = BLANK (), 1, DIVIDE ( _currentyearcount - precount, precount ) )

 

 shot growth.JPG

 

Paul

 

Anonymous
Not applicable

Hi @V-pazhen-msft can you please share the Pbix file for this, i am now not able to solve this issue. 

@Anonymous 

The pibx link is already posted above.

Anonymous
Not applicable

@V-pazhen-msft  i am not able to access the file. can you please share it with me.

Anonymous
Not applicable

@V-pazhen-msft

 

Thanks for this i have now got yearly and quarterly but unfortunately monthly is throwing error:

 

Monthly_growth=
VAR currentyear = [CREATED].[Year]
VAR currmonth = [CREATED].[Month]
VAR currmonth =
CALCULATE ([Count of users],
FILTER ( 'Table', 'Table'[CREATED].[Month]= currmonth ),
'Table'[year123]= currentyear
)
VAR prevmonth =
CALCULATE ([Count of users],
ALL ( 'Table' ),
PREVIOUSMONTH('Table'[CREATED].[Date])
)

RETURN
IF (
prevmonth = BLANK (),
1,
DIVIDE ( currmonth - prevmonth, prevmonth )
)


It shows that the variable currmonth  is already in use were as i have never used that vaiable before 
Anonymous
Not applicable

Hi @V-pazhen-msft  i was trying to move these measures to SSAS tabular model cube and facing challenges. Can you please let me know if these could be converted to measures or moved to cube ?

 

Thanks in advance

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.