Forum Discussion

Zaynah16's avatar
Zaynah16
Helper I
4 years ago

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...
ValtteriN's avatar
ValtteriN
Community Champion
4 years ago

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!
Zaynah16's avatar
Zaynah16
Helper I
4 years ago

Hi  ValtteriN, 

 

Thank you for the above, would it possible for you to drop the powr bi file that you did the example in here 

 

Thanks 

Zaynah

 

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    Hi,

    Unfortunately I am not able to provide the pbix file. 

    • Zaynah16's avatar
      Zaynah16
      Helper I
      4 years ago

      My Column seems to not be working and returned blank values ? ValtteriN 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Zaynah16 ,

        Could you please share some sample data (exclude sensitive data) and your expected result with calculation logic and special examples in order to make troubleshooting and provide you a suitable solution? It is better if you can provide a simplified pbix file. Thank you.

        In addition, it seems that you are trying to get the difference between the current year and the previous year and then do the rest of the calculations based on your description. If that is the case, you can refer to the following blog to implement it.

        How to compare Last year and Current Year sales in Power BI?

        CREATING A POWER BI CHART COMPARING EACH YEAR VS PREVIOUS YEAR

        Best Regards