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.

 

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.

Need Help
Tips and Tricks

1 Reply

  • 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