'Calculate aggregate value of last 12 months in a Measure Power BI
I'm using this measure to calculate a aggregate sum of a value in the last 12 months. The measure is working well if I start using it from the month 12. But, the problem is, if the month is not in the 12 or higher, the value is not right.
Example, if you are in the first month of the sample, I would like to multiply this value by 12 (1st month + 11 months). If it was the second month, I'd like you to average the two months and multiply it by 12. And so on.
could you please help me?
SumRevenue =
var vSumNet12 =
CALCULATE(
Table[Trevenue],
DATESINPERIOD(
CalendarM[Data],
MAX(CalendarM[Data]),
-12,
MONTH
)
)
return
vSumNet12
Example table:
Date Customer Net Trevenue SumRevenue ROA ROA I Want
09/30/20 A 237767115,6 327444,2478 327444,2478 0,14% 1,65%
10/31/20 A 245689276,3 251934,78 579379,0278 0,24% 1,41%
11/30/20 A 252916933,6 262294,89 841673,9178 0,33% 1,33%
12/31/20 A 241424127 509883,07 1351556,988 0,56% 1,68%
01/31/21 A 244721140,9 259250 1610806,988 0,66% 1,58%
02/28/21 A 250913741,4 246740,33 1857547,318 0,74% 1,48%
03/31/21 A 282215365,7 550897,35 2408444,668 0,85% 1,46%
04/30/21 A 312759343,1 544161,63 2952606,298 0,94% 1,42%
05/31/21 A 325535894 419360,97 3371967,268 1,04% 1,38%
06/30/21 A 371306315 390650,41 3762617,678 1,01% 1,22%
07/31/21 A 379780645,3 527254,43 4289872,108 1,13% 1,23%
08/31/21 A 415390274,9 409196,3 4699068,408 1,13% 1,13%
09/30/21 A 433837730,6 598924,02 4970548,18 1,15% 1,15%
10/31/21 A 482659906,7 254086,32 4972699,72 1,03% 1,03%
11/30/21 A 501568104,7 318924,53 5029329,36 1,00% 1,00%
12/31/21 A 507124350,5 754897,79 5274344,08 1,04% 1,04%
01/31/22 A 510220304,2 179153,11 5194247,19 1,02% 1,02%
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
