Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
67 | |
58 | |
50 | |
36 | |
34 |
User | Count |
---|---|
86 | |
72 | |
58 | |
45 | |
44 |