Forum Discussion
Need help with a formula: difference compared to previous quarter when quarters are custom.
Hi AurelieP ,
You can try measure formula like below:
Result = VAR cur = MAX ( YourTable[Quarter number] ) VAR cur_leads = CALCULATE ( SUM ( 'YourTable'[# Leads] ), FILTER ( ALL ( YourTable ), 'YourTable'[Quarter number] = cur - 1 ) ) VAR difference = MAX ( YourTable[# Leads] ) - cur_leads RETURN IF ( cur - 1 <> 0, DIVIDE ( difference, cur_leads ), BLANK () )Best Regards,
Adamk KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Community Championin order to give you the best possible solution, please provide some sample data with an example of what you are expecting to see and how?
Hello ,
Here is an example, I want to calculate the difference and the variation
The difference is number of leads in a specific quarter - number of leads 1 quarter before.
The variation is ((leads in a specific quarter − leads 1 quarter before) / leads 1 quarter before)×100
I also put the formulas in the 1st 2 rows above.
I know these 2 fields need to be measures and to calculate it I probably need to use the quarter number as a reference and -1 for the previous quarter.. Hope this helps
| Quarter number | # Leads | Difference | Variation |
| 1 | 15 | ||
| 2 | 25 | 25-15= 10 | ((25-15)/15)*100= 67% |
| 3 | 150 | 150-25= 125 | ((150-25)/55)*100= 500% |
| 4 | 75 | -75 | -50% |
| 5 | 12 | -63 | -84% |
| 6 | 15 | 3 | 25% |
| 7 | 20 | 5 | 33% |
| 8 | 30 | 10 | 50% |
| 9 | 10 | -20 | -67% |
| 10 | 50 | 40 | 400% |
| 11 | 25 | -25 | -50% |
| 12 | 15 | -10 | -40% |
| 13 | 30 | 15 | 100% |
| 14 | 60 | 30 | 100% |
| 15 | 14 | -46 | -77% |
| 16 | 35 | 21 | 150% |
| 17 | 20 | -15 | -43% |
| 18 | 18 | -2 | -10% |
| 19 | 100 | 82 | 456% |
Hi AurelieP ,
You can try measure formula like below:
Result = VAR cur = MAX ( YourTable[Quarter number] ) VAR cur_leads = CALCULATE ( SUM ( 'YourTable'[# Leads] ), FILTER ( ALL ( YourTable ), 'YourTable'[Quarter number] = cur - 1 ) ) VAR difference = MAX ( YourTable[# Leads] ) - cur_leads RETURN IF ( cur - 1 <> 0, DIVIDE ( difference, cur_leads ), BLANK () )Best Regards,
Adamk KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly