Current month minus Previous month

[Digger]

_Forecast, % =
VAR ForecastMinimumPercent = 10
VAR ForecastIncreasePercent = 15
VAR sM = MAX(T1[PlanDate]) //Plan Date
VAR fM = MAX('_Date2'[Date]) //fact Date
VAR sC = FIRSTNONBLANK(T1[ProjectCode],1) //Current project code
VAR tP = //Current forecast cumulative percent
CALCULATE(
SUM(T1[ForecastPercent]),
FILTER(
ALLSELECTED('T1'),
T1[PlanDate] <= fM
&&
T1[PlanDate] <= sM
&&
T1[ProjectCode] = sC
)
,REMOVEFILTERS (T1[PlanDate])
)
VAR mM = // Current Project first Date
CALCULATE(
MIN(T1[PlanDate]),
FILTER(
ALLSELECTED('T1'),
T1[ProjectCode] = sC
)
,REMOVEFILTERS (T1[PlanDate])
)
VAR nD = ROUNDUP(DIVIDE(100-tP, ForecastIncreasePercent, 0),0) // count of forecast columns
VAR fD = DATEDIFF(sM, fM, MONTH) //dates offsets, '_Date2'[Date] - T1[PlanDate]
VAR P1 = tP + ForecastIncreasePercent * fD //forecasted percent
VAR P2 = IF(P1 > 100, 100, P1) //does not show values over 100, for example 90+15=105 but show 100
VAR fP =
SWITCH(TRUE(),
tP < ForecastMinimumPercent, BLANK(), // if percent <10, then hide
fM < mM, BLANK(), //hide values if T1[PlanDate] <> '_Date2'[Date]
MAX(T1[ForecastPercent])=0, BLANK(), // hide empty percent rows
fM > DATE(YEAR(sM), MONTH(sM) + nD + 1, DAY(sM)), BLANK(), //does not show 100 more than once
fM <= sM, IF(HASONEVALUE(T1[PlanDate]),BLANK(), tP), //if collapsed show first fact values
P2
)
RETURN
fP

This measure give me current forecast percent

But i need get difference current month - previous

[v-zhangti]

Hi, @Digger 

Can you provide a simple pbix file? Besides, I don't understand why this is equal to 15. Can you explain the logic here?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Digger]

@v-zhangti  yep there is mistake, should be 5
I corrected some measure and now getting good sums multiplied by forecast percent, but problem:
1. Not sure if my measure good for performance
2. Cant get good totals on rows and on columns

https://file.io/ve4zm2YhRIe5

[v-zhangti · Accepted solution]

Hi, @Digger 

You can try the following methods.

Measure:

Measure = 
VAR _Current=CALCULATE([_Forecast, %])
Var _Previou=CALCULATE([_Forecast, %],DATEADD(_Date2[Date],-1,MONTH))
Return
Switch(TRUE(),
_Current=BLANK(),BLANK(),
_Previou=BLANK(),BLANK(),_Current-_Previou)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.