Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I'm trying to show the percentage growth of numbers of events in a process by month/week, but I can't achieve it. I have used the measure below, but it only shows the total growth, and not by month.
Date | Which stage is the current property at |
24/08/2021 11:54 | Stage 5 |
07/09/2021 10:42 | Stage 1 |
07/09/2021 11:11 | Stage 1 |
09/09/2021 08:37 | Stage 1 |
09/09/2021 10:58 | Stage 5 |
09/09/2021 14:48 | Stage 4 |
13/09/2021 11:19 | Stage 4 |
16/09/2021 09:10 | Stage 4 |
20/09/2021 12:22 | Stage 1 |
21/09/2021 09:27 | Stage 4 |
07/10/2021 09:07 | Stage 3 |
14/10/2021 16:57 | Stage 4 |
04/11/2021 17:05 | Stage 2 |
09/11/2021 16:55 | Stage 4 |
23/11/2021 09:00 | Stage 4 |
01/12/2021 12:28 | Stage Other |
02/12/2021 10:38 | Stage 4 |
Does anybody know have an idea on how to make to show the growth by month and by stage?
Thanks
Solved! Go to Solution.
Hi, @ADEI20 ;
Try it.
% Change =
var _curr=CALCULATE(COUNT(Data[Which stage is the current property at]),FILTER(ALLSELECTED(Data),EOMONTH([Date],0)=EOMONTH(MAX([Date]),0)&&[Which stage is the current property at]=MAX([Which stage is the current property at])))
var _pre=CALCULATE(COUNT(Data[Which stage is the current property at]),FILTER(ALLSELECTED(Data),EOMONTH([Date],0)=EOMONTH(MAX([Date]),-1)&&[Which stage is the current property at]=MAX([Which stage is the current property at])))
return DIVIDE(_curr,_pre,1)-1
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi, @ADEI20 ;
Try it.
% Change =
var _curr=CALCULATE(COUNT(Data[Which stage is the current property at]),FILTER(ALLSELECTED(Data),EOMONTH([Date],0)=EOMONTH(MAX([Date]),0)))
var _pre=CALCULATE(COUNT(Data[Which stage is the current property at]),FILTER(ALLSELECTED(Data),EOMONTH([Date],0)=EOMONTH(MAX([Date]),-1)))
return DIVIDE(_curr,_pre,1)-1
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yalanwu-msft the % changes are by a total of each month, but when split by stage it doesn't work. E.g. in Stage 4 from Sep (4 entries) to Oct (1 entry), it should show -75%. Is there a way to show by month and by stage?
Hi, @ADEI20 ;
Try it.
% Change =
var _curr=CALCULATE(COUNT(Data[Which stage is the current property at]),FILTER(ALLSELECTED(Data),EOMONTH([Date],0)=EOMONTH(MAX([Date]),0)&&[Which stage is the current property at]=MAX([Which stage is the current property at])))
var _pre=CALCULATE(COUNT(Data[Which stage is the current property at]),FILTER(ALLSELECTED(Data),EOMONTH([Date],0)=EOMONTH(MAX([Date]),-1)&&[Which stage is the current property at]=MAX([Which stage is the current property at])))
return DIVIDE(_curr,_pre,1)-1
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
It is better to show What is the outcome look like to give a better solution!
Proud to be a Super User!
This is the output I have got.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |