Help with DAX formula, calculate % of growth in the last 7 days

[Anonymous]

Hi Guys,

Please, need help with a DAX Formula, this is the table in PowerBI

array_name	fs_name	Total GB	Used GB	Free GB	Used%	Last Collection Date
UKLONRAPVNX01	FS_UKLONDEYFS09_01	7878	7878	0	100,00%	19/12/2018 00:00
USSECSFVNX001	test_SNMP_TEMP3	5	5	0	100,00%	19/12/2018 00:00
USSECSFVNX001	FS_USSECVAPFL43_02	14771	14723	48	99,68%	19/12/2018 00:00
INDELHMEYNAS02	Del_TAXFS01	6226	6201	25	99,60%	19/12/2018 00:00
INDELHMEYNAS02	Del_AuditFS01	1746	1738	8	99,54%	19/12/2018 00:00
INGURSUVNX001	FS_INGURVAPFL10_01	8141	8086	55	99,32%	19/12/2018 00:00
USSECSFVNX001	FS_USSECVAPFL35_01	10084	10003	80	99,20%	19/12/2018 00:00
INDELHMEYNAS02	Del_TASFS01	246	244	2	99,19%	19/12/2018 00:00
INDELHMEYNAS02	DEL_AFS01	4850	4808	42	99,13%	19/12/2018 00:00
DEFRNSFVNX001	FR_DEFRANMCEFL08_04	13934	13586	348	97,50%	19/12/2018 00:00
CATORVNX02	FS_CATORVAPFL50_08	2017	1933	84	95,84%	19/12/2018 00:00
SGSINSFVNX003	FS_SGSINVAPFL20_09	3592	3427	165	95,41%	19/12/2018 00:00
DERUSSFVNX007	FS_DEFRAVAPFL21_01	16117	15134	984	93,90%	19/12/2018 00:00
DEFRNSFVNX001	FR_DEFRANMCEFL08_03	14117	13133	984	93,03%	19/12/2018 00:00
CATORVNX02	FS_CATORVAPFL21_03	7878	7312	565	92,82%	19/12/2018 00:00
ZAJOHRAPVNX01	FS_ZAJOHAMEYNAS03	11092	10201	891	91,97%	19/12/2018 00:00
DERUSSFVNX007	FS_DEFRAVAPFL21_08	15630	14332	1298	91,70%	19/12/2018 00:00
INHYDRAPVNX01	FA_INHYDVAPFL10_05	6050	5523	528	91,29%	19/12/2018 00:00
DEFRNSFVNX001	FR_DEFRANMCEFL08_01	9847	8954	894	90,93%	19/12/2018 00:00
CATORVNX02	FS_CATORVAPFL11_01	7878	7134	744	90,56%	19/12/2018 00:00
DEFRNSFVNX007	FS_DEFRAVAPFL14_01	11395	10297	1098	90,36%	19/12/2018 00:00
DEFRNSFVNX001	FR_DEVIDVAPFL35_01	10084	9084	999	90,08%	19/12/2018 00:00

So as you can see i have the columns fs_name, used_GB, Total GB, Free GB, Used %.  So i would like to add other column with the % used space Growth in the las 7 days, and also in the las 15 days, 

The idea will to create a dax to calculate the % used in the erlierst date and then substract with the today % used information.

Thank you!

[v-lili6-msft]

hi, @Anonymous

What is your expected output? what is "the last 7 days"?  is it the last 7 days of max([Last Collection Date]) for each [fs_name]?

Please share some data sample and expected output. This will help us better understand your needs.

Best Regards,

Lin

[Anonymous]

hi @v-lili6-msft thanks for your answer,

the last 7 day is the calculation beetween the "today date" and the 7 day back, 

Example: 

table example_1:

fs_name	Total GB	Used GB	Free GB	Used%	Last Collection Date	Used_Per
FR_DEFRANMCEFL08_03	14117	13181	936	93,37%	8/1/2019 00:00	93,37%
FR_DEFRANMCEFL08_03	14117	13166	951	93,26%	7/1/2019 00:00	93,26%
FR_DEFRANMCEFL08_03	14117	13159	959	93,21%	6/1/2019 00:00	93,21%
FR_DEFRANMCEFL08_03	14117	13158	959	93,21%	4/1/2019 00:00	93,21%
FR_DEFRANMCEFL08_03	14117	13158	959	93,21%	5/1/2019 00:00	93,21%
FR_DEFRANMCEFL08_03	14117	13149	969	93,14%	3/1/2019 00:00	93,14%
FR_DEFRANMCEFL08_03	14117	13147	970	93,13%	2/1/2019 00:00	93,13%
FR_DEFRANMCEFL08_03	14117	13143	974	93,10%	31/12/2018 00:00	93,10%
FR_DEFRANMCEFL08_03	14117	13143	974	93,10%	1/1/2019 00:00	93,10%
FR_DEFRANMCEFL08_03	14117	13141	976	93,09%	28/12/2018 00:00	93,09%
FR_DEFRANMCEFL08_03	14117	13141	976	93,09%	29/12/2018 00:00	93,09%
FR_DEFRANMCEFL08_03	14117	13141	976	93,09%	30/12/2018 00:00	93,09%
FR_DEFRANMCEFL08_03	14117	13140	977	93,08%	27/12/2018 00:00	93,08%
FR_DEFRANMCEFL08_03	14117	13139	979	93,07%	21/12/2018 00:00	93,07%
FR_DEFRANMCEFL08_03	14117	13139	979	93,07%	22/12/2018 00:00	93,07%
FR_DEFRANMCEFL08_03	14117	13139	979	93,07%	23/12/2018 00:00	93,07%
FR_DEFRANMCEFL08_03	14117	13139	978	93,07%	24/12/2018 00:00	93,07%
FR_DEFRANMCEFL08_03	14117	13139	978	93,07%	25/12/2018 00:00	93,07%
FR_DEFRANMCEFL08_03	14117	13139	978	93,07%	26/12/2018 00:00	93,07%
FR_DEFRANMCEFL08_03	14117	13138	979	93,07%	20/12/2018 00:00	93,07%
FR_DEFRANMCEFL08_03	14117	13133	984	93,03%	19/12/2018 00:00	93,03%
FR_DEFRANMCEFL08_03	14117	13118	999	92,92%	18/12/2018 00:00	92,92%
FR_DEFRANMCEFL08_03	14117	13116	1001	92,91%	17/12/2018 00:00	92,91%

The idea is to use fs_name from table example_1 

Check the last date: 8/1/2019 00:00  and compare the value used_per with the previus date of 7 day before, 30/12/2018 00:00 

The value from 30/12/2018 is = 93,09%

The value from 8/01/2019 is = 93,37 % 

So the result would be = 93,37 % - 93,09% = 0,28 %

in that case in the last 7 days the fs_name growth in space 0,28% 

The idea would be have a dax  to place the result in the same table of example, with the name of the column " 7 days growth " 

Thanks

[v-lili6-msft · Accepted solution]

hi, @Anonymous

First, the last 7 day is the calculation beetween the "today date" and the 7 day back, 

The idea is to use fs_name from table example_1 

Check the last date: 8/1/2019 00:00  and compare the value used_per with the previus date of 7 day before, 30/12/2018 00:00 

for your example,

today date (8/01/2019) is the max date of [Last Collection Date] ? and the last 7 days (30/12/2019) is (today date -9)?

If so you could try use this formula to create a column as below:

Column = var _maxday=CALCULATE(MAX(Table1[Last Collection Date]),FILTER(Table1,Table1[fs_name]=EARLIER(Table1[fs_name])))return
CALCULATE(SUM(Table1[Used%]),FILTER(Table1,Table1[fs_name]=EARLIER(Table1[fs_name])&&Table1[Last Collection Date]=_maxday))-
CALCULATE(SUM(Table1[Used%]),FILTER(Table1,Table1[fs_name]=EARLIER(Table1[fs_name])&&Table1[Last Collection Date]=_maxday-9))

and  if last 7 day date is not (_maxday-9), you could adjust it by yourself.

Best Regards,

Lin