Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi All,
My SQL query contains two dynamic values columns as well as a while loop condition. The two columns will be updated based on the While loop condition.
I need to create a table in Power Bi based on the query below.
---------------------------------------------------------------------------------------------------------------------
DECLARE @evaldate datetime = DATEADD(DD, -1, DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0))
DECLARE @SQLString NVARCHAR(Max)
SET @SQLString = '
Select CONVERT(varchar(10), @accStart, 101) + ''-'' + CONVERT(varchar(10), @accEnd, 101) as [Accident_Period],
((YEAR(@evaldate) - YEAR(@accEnd) + 1)*12 + (MONTH(@evaldate) - MONTH(@accEnd))) as [Age]
FROM Dev.FCClaimDailySnapshot
'
DECLARE @accStart datetime, @accEnd datetime
WHILE YEAR(@evaldate) >= YEAR(GETDATE())-20
BEGIN
SET @accEnd = @evaldate
SET @accStart = DATEADD(DD, +1, EOMONTH(@accEnd, -12))
WHILE YEAR(@accEnd) >= YEAR(GETDATE()) -20
------------------
BEGIN
--SELECT @evaldate as [Eval], @accStart as [Acc_Start], @accEnd as [Acc_End] --for Testing dateloop
EXEC sp_executesql @SQLString,
N'@evaldate datetime, @accStart datetime, @accEnd datetime',
@evaldate = @evaldate, @accStart = @accStart, @accEnd = @accEnd
SET @accEnd = EOMONTH(@accEnd, -12)
SET @accStart = DATEADD(DD, +1, EOMONTH(@accEnd, -12))
END
-------------------------------------------
SET @evaldate = EOMONTH(@evaldate, -12)
END
@amitchandakThank you for responding. thank you very much..........!😊
You can see an example of the query's output for five years' of data here. I need to display 20 years' of data starting from this date WHILE YEAR(@accEnd) >= YEAR(GETDATE()) -20.
@Anonymous , You can use expression functions like sumx, minx, etc
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
User | Count |
---|---|
72 | |
66 | |
34 | |
25 | |
22 |
User | Count |
---|---|
96 | |
94 | |
59 | |
45 | |
42 |