Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
ssspk
Helper III
Helper III

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.

 

Query:(Azure databricks query)
------
SELECT to_date(servertime,'yyyy-MM-dd') as Date, count(*) as Count,
LAG(count(*)) OVER (ORDER BY to_date(servertime,'yyyy-MM-dd')) AS count_prev,
count(*) + LAG(count(*), 1, 0) OVER (ORDER BY to_date(servertime,'yyyy-MM-dd')) AS Incremental_Total
from traces_p.data
INNER JOIN traces_p.type on data.type = type.id
WHERE typename in ('bookmark','app')
GROUP BY to_date(servertime,'yyyy-MM-dd')
ORDER BY 1;
Result:
-------
Date                       Count       count_prev     Incremental_Total
2023-07-17            1209           null               1209
2023-07-18           1454           1209              2663
2023-07-19          1416            1454              2870
2023-07-20         1284            1416               2700
2023-07-21         1420            1284               2704
2023-07-22          48               1420               1468
2023-07-23          103              48                   151
2023-07-24         2093            103                 2196
2023-07-25         1755            2093               3848
2023-07-26         1555           1755                3310

 

powerbi datapowerbi data

 

 

 

 

 

 

 

How to use this query in power bi? Any DAX query ? or Formula can be used?

Thank in Advance.

1 REPLY 1
amitchandak
Super User
Super User

@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

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.