topic Re: LAG FUNCTION DAX in DAX Commands and Tips

LAG FUNCTION DAX

Zaynah16 — Thu, 09 Dec 2021 09:54:37 GMT

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

Re: LAG FUNCTION DAX

amitchandak — Thu, 09 Dec 2021 11:34:26 GMT

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" ))

Re: LAG FUNCTION DAX

ValtteriN — Thu, 09 Dec 2021 11:34:49 GMT

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!

Re: LAG FUNCTION DAX

Zaynah16 — Mon, 13 Dec 2021 06:41:34 GMT

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

Re: LAG FUNCTION DAX

ValtteriN — Mon, 13 Dec 2021 07:06:18 GMT

Hi,

Unfortunately I am not able to provide the pbix file.

Re: LAG FUNCTION DAX

Zaynah16 — Mon, 13 Dec 2021 07:07:29 GMT

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

Re: LAG FUNCTION DAX

Anonymous — Fri, 17 Dec 2021 10:10:08 GMT

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