Forum Discussion

ssspk's avatar
ssspk
Helper III
2 years ago

SQL Previous rows value based on date (Lag fn from SQL in Power BI logic) usage?

Hello Team,   How to convert the below Azure  databricks SQL query  in power bi? I have to use "Incremental_Total" in Y axis and "Date" in the X axis for the line chart display in power bi.   Qu...
Need Help
Tips and Tricks
amitchandak's avatar
amitchandak
Super User
2 years ago

ssspk , Consider offset function

examples

 

Offset Previous Month = CALCULATE([Net],OFFSET(-1, ALL('Date'[Month Year sort],'Date'[Month Year]),ORDERBY('Date'[Month Year sort])))

 

Offset Previous Rank = CALCULATE(Sum([Revenue]),OFFSET(-1, ALL('Table'[Name],'Table'[Rank]),ORDERBY('Date'[Rank])))

 

Previous Month in Year = CALCULATE([Net],OFFSET(-1, ALL('Date'[Month Year sort],'Date'[Month Year],'Date'[Year],'Date'[Qtr Year]),ORDERBY('Date'[Month Year sort]),KEEP,PARTITIONBY('Date'[Year])))

 

Offset Previous Day = CALCULATE([Avg Price],OFFSET(-1, ALL('Table'[Published Date],'Table'[Index]),ORDERBY('Date'[Published Date]),,partitionBy('Table'[Index])))

 

Offset Previous Week = CALCULATE([Pax],OFFSET(-1, ALL('Table'[Week],'Table'[S. No]),ORDERBY('Table'[S. No])))

 

Offset Previous Row = CALCULATE(Max('Table'[Time]) ,OFFSET(-1, ALL('Table'[Person],'Table'[Time]),ORDERBY('Table'[Time],asc),, PARTITIONBY('Table'[Person]) ))

 

Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U