Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
@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
User | Count |
---|---|
84 | |
77 | |
76 | |
43 | |
36 |
User | Count |
---|---|
109 | |
56 | |
52 | |
45 | |
43 |