Forum Discussion
LAG FUNCTION DAX
Hi Guys, Please can someone help with a lag function. Calculation needed : Working Platform Conversion = Buying Platform(This Year BP- LY BP )/Last year WP Required output in BI S...
Hi,
I would recommend doing this using measures but here is a calculated column example:
Example data:
Calculated column:
WPC = var thisYearS = DATE(YEAR(TODAY()),1,1)
var thisYearE = date(YEAR(TODAY()),12,31)
var lastyearS =DATE(YEAR(TODAY())-1,1,1)
var LastYearE = date(YEAR(TODAY())-1,12,31)
Var thisYearBP = CALCULATE(SUM(Cumulativetotal[Value]),ALL(Cumulativetotal),DATESBETWEEN('Cumulativetotal'[Date],thisYearS,thisYearE),Cumulativetotal[Area]="BP")
var lastYearBP = CALCULATE(SUM(Cumulativetotal[Value]),ALL(Cumulativetotal),DATESBETWEEN('Cumulativetotal'[Date],lastYearS,lastYearE),Cumulativetotal[Area]="BP")
var lastYearWP = CALCULATE(SUM(Cumulativetotal[Value]),ALL(Cumulativetotal),DATESBETWEEN('Cumulativetotal'[Date],lastYearS,lastYearE),Cumulativetotal[Area]="WP")
return
Divide(thisYearBP-lastYearBP,lastYearWP)
End result:
Measure example for this year BP:
ThisyearBP =
var _sdate= DATE(YEAR(TODAY()),1,1)
var _edate = DATE(YEAR(TODAY()),12,31)
return
CALCULATE(SUM(Cumulativetotal[Value]),DATESBETWEEN(Cumulativetotal[Date],_sdate,_edate),Cumulativetotal[Area]="BP")
Hopefully this helps to resolve your issue and if it deos consider accepting this as a solution!
