Re: LAG FUNCTION DAX

Zaynah16 · ‎12-09-2021

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

Single column with the value WPC

Data Structure

Table 1

pillar BU name region od name market value date

XXX xx x x xx x BP JAN2021

YYY yy x x xx x BP FEB 2021

ZZZ zz x x xx x BP MAR 2021

AAA AA x x xx x BP APR 2021

BBB BB x x xx x BP MAY 2021

CCC CC x x xx x BP JUNE 2021

XXX xx x x xx x BP JULY2021

YYY yy x x xx x BP AUG 2021

ZZZ zz x x xx x BP SEP 2021

AAA AA x x xx x BP OCT 2021

BBB BB x x xx x BP NOV 2021

CCC CC x x xx x BP DEC 2021

XXX xx x x xx x WP JAN2021

YYY yy x x xx x WP FEB 2021

ZZZ zz x x xx x WP MAR 2021

AAA AA x x xx x WP APR 2021

BBB BB x x xx x WP MAY 2021

CCC CC x x xx x WP JUNE 2021

XXX xx x x xx x WP JULY2021

YYY yy x x xx x WP AUG 2021

ZZZ zz x x xx x WP SEP 2021

AAA AA x x xx x WP OCT 2021

BBB BB x x xx x WP NOV 2021

CCC CC x x xx x WP DEC 2021

XXX xx x x xx x BP JAN2022

YYY yy x x xx x BP FEB 2022

ZZZ zz x x xx x BP MAR 2022

AAA AA x x xx x BP APR 2022

BBB BB x x xx x BP MAY 2022

CCC CC x x xx x BP JUNE 2022

XXX xx x x xx x BP JULY2022

YYY yy x x xx x BP AUG 2022

ZZZ zz x x xx x BP SEP 2022

AAA AA x x xx x BP OCT 2022

BBB BB x x xx x BP NOV 2022

CCC CC x x xx x BP DEC 2022

XXX xx x x xx x WP JAN2022

YYY yy x x xx x WP FEB 2022

ZZZ zz x x xx x WP MAR 2022

AAA AA x x xx x WP APR 2022

BBB BB x x xx x WP MAY 2022

CCC CC x x xx x WP JUNE 2022

XXX xx x x xx x WP JULY2022

YYY yy x x xx x WP AUG 2022

ZZZ zz x x xx x WP SEP 2022

AAA AA x x xx x WP OCT 2022

BBB BB x x xx x WP NOV 2022

CCC CC x x xx x WP DEC 2022

ValtteriN · ‎12-09-2021

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!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Zaynah16 · ‎12-12-2021

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 · ‎12-12-2021

Hi,

Unfortunately I am not able to provide the pbix file.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Zaynah16 · ‎12-12-2021

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

Anonymous · ‎12-17-2021

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

amitchandak · ‎12-09-2021

@Zaynah16 , You need time intelligence with date table and additional filter

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

example

YTD Sales BP= CALCULATE(Countrows(Table),DATESYTD('Date'[Date],"12/31"), filter(Table, Table[Market] = "BP" ))
Last YTD Sales BP = CALCULATE(Countrows(Table),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"), filter(Table, Table[Market] = "BP" ))

YTD Sales WP= CALCULATE(Countrows(Table),DATESYTD('Date'[Date],"12/31"), filter(Table, Table[Market] = "WP" ))
Last YTD Sales WP = CALCULATE(Countrows(Table),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"), filter(Table, Table[Market] = "WP" ))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube