Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Year | Count | Previous Year count | Growth |
2017 | 10 | 100% | |
2018 | 25 | 10 | 150% |
2019 | 37 | 25 | 48% |
2020 | 42 | 37 | 13% |
In quarter looking for below: when we go to new year quarter previous year last quarter should be considered.
Quarter | Count | Previous Quarter | Growth |
Qtr4 2018 | 10 | 100% | |
Qtr1 2019 | 25 | 10 | 150% |
Qtr 2 2019 | 37 | 25 | 48% |
Qtr 3 2019 | 42 | 37 | 13% |
used this formula but not able to accomdate previous year Quarter data:
Solved! Go to 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 ) )
Paul
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))
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))
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.
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 !!
NAME | CREATED | COMPANY_ID |
ddd | 10/17/2018 23:23 | 440 |
B | 10/17/2018 23:45 | 500 |
C | 10/17/2018 23:56 | 540 |
D | 10/17/2018 23:29 | 441 |
E | 10/18/2018 0:02 | 560 |
F | 10/18/2018 0:02 | 0 |
GH | 10/18/2018 0:02 | 2 |
B | 10/17/2018 19:47 | 420 |
A | 10/17/2018 19:52 | 421 |
B | 10/17/2018 23:35 | 460 |
A | 10/17/2018 19:59 | 422 |
B | 10/17/2018 23:51 | 520 |
hj | 10/17/2018 19:18 | 400 |
li | 10/17/2018 23:40 | 480 |
hhgg | 11/28/2018 16:22 | 680 |
vft | 12/7/2018 11:48 | 920 |
ntt | 1/8/2019 12:29 | 1120 |
c1 | 1/30/2019 15:47 | 1280 |
jwhe | 1/8/2019 12:50 | 1161 |
One | 1/17/2019 21:52 | 1260 |
AAA | 2/11/2019 15:14 | 1300 |
P1 | 2/12/2019 10:27 | 1320 |
bb | 2/15/2019 16:17 | 1440 |
TEST CUSTOMER | 2/20/2019 13:50 | 1520 |
led | 3/28/2019 15:33 | 1820 |
kdmncdi | 4/10/2019 10:51 | 1940 |
demo | 4/26/2019 12:45 | 2060 |
Cash | 7/4/2019 15:02 | 2520 |
majrj | 7/9/2019 11:31 | 2560 |
mjho | 8/1/2019 13:02 | 2760 |
kvr | 8/23/2019 11:17 | 2860 |
riijv | 5/18/2019 16:32 | 2200 |
msfjfmam | 5/25/2019 16:00 | 2360 |
ahvu | 8/26/2019 23:36 | 2880 |
cc | 8/27/2019 12:43 | 2901 |
arih | 7/26/2019 22:24 | 2704 |
akeur | 8/27/2019 14:01 | 2902 |
skruh | 8/27/2019 14:16 | 2903 |
sjrh | 9/4/2019 11:22 | 2920 |
nsrh | 9/30/2019 19:03 | 2980 |
jsruhg | 10/19/2019 3:33 | 3260 |
fjhvjsr | 10/4/2019 13:13 | 3060 |
ruhf | 10/21/2019 15:17 | 3280 |
ruhf | 11/28/2018 13:36 | 600 |
ar | 11/28/2018 14:17 | 621 |
ori | 11/28/2018 14:48 | 640 |
sro | 12/3/2018 14:37 | 760 |
srrr | 12/3/2018 16:53 | 780 |
ropri | 12/4/2018 10:52 | 801 |
aruhv | 12/4/2018 15:22 | 820 |
ruhv | 1/7/2019 16:56 | 1080 |
bbb | 2/13/2019 10:26 | 1380 |
cpty7 | 2/15/2019 12:31 | 1400 |
NCP | 3/1/2019 17:55 | 1640 |
rif | 2/19/2019 17:17 | 1500 |
rifur | 3/8/2019 15:19 | 1660 |
lro | 3/14/2019 17:49 | 1680 |
rijfrjg | 3/14/2019 18:18 | 1681 |
oyp | 3/15/2019 16:24 | 1700 |
qwe | 3/19/2019 12:27 | 1720 |
fvhv | 3/20/2019 15:07 | 1740 |
fvfvvv | 3/20/2019 16:58 | 1760 |
api | 3/29/2019 18:30 | 1840 |
duser | 4/1/2019 15:17 | 1860 |
@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 ) )
Paul
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.
@V-pazhen-msft i am not able to access the file. can you please share it with me.
@Anonymous
Sorry for that, this one should work.
Best
Thanks for this i have now got yearly and quarterly but unfortunately monthly is throwing error:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |