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

[ssspk]

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 data

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

Thank in Advance.

[amitchandak]

@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