Forum Discussion
cristianml
Post Prodigy
6 years agoCalculate % increase from last Period (Month)
Hi, I would like to calculate the % increase of a measure from one month to another : Actual LCR = CALCULATE(AVERAGE('Resource Actual'[Quantity]),'Resource Actual'[Category]="Cost Rate") I ...
Anonymous
6 years agoNot applicable
You've got the variance caluclation correct, you just need to actually calculate the previous month.
Actual LCR = CALCULATE(
AVERAGE('Resource Actual'[Quantity])
,'Resource Actual'[Category] = "Cost Rate"
)
Actual LCR LM =
VAR last_date = LASTDATE(ALL('Resource Actual'[Date Column]))
VAR current_date = LASTNONBLANK(DateDimension[Date],[Actual LCR])
VAR ly_date = NEXTDAY(PREVIOUSMONTH(current_date))
VAR date_context =
DATESBETWEEN(
DateDimension[Date]
,NEXTDAY(PREVIOUSMONTH(current_date))
,LASTDATE(current_date)
)
VAR result = CALCULATE([Actual LCR],DATEADD(date_context,-1,MONTH))
RETURN
result
Variance = DIVIDE(
[Actual LCR] - [Actual LCR LM]
,[Actual LCR LM]
) * SIGN([Actual LCR LM])This should get you what you want if not let me know.
cristianml
Post Prodigy
6 years agoHi Anonymous ,
Could you please help me to fix the error ?:
Thanks.
- Anonymous6 years agoNot applicable
Those're actually two separate measures.
Measure 1: Last Months Actual LCR
Actual LCR LM = VAR last_date = LASTDATE(ALL('Resource Actual'[Date Column])) VAR current_date = LASTNONBLANK(DateDimension[Date],[Actual LCR]) VAR ly_date = NEXTDAY(PREVIOUSMONTH(current_date)) VAR date_context = DATESBETWEEN( DateDimension[Date] ,NEXTDAY(PREVIOUSMONTH(current_date)) ,LASTDATE(current_date) ) VAR result = CALCULATE([Actual LCR],DATEADD(date_context,-1,MONTH)) RETURN resultMeasure 2: Variance
Variance = DIVIDE( [Actual LCR] - [Actual LCR LM] ,[Actual LCR LM] ) * SIGN([Actual LCR LM])