Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

How do deal with a while loop in Power Bi?

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

2 REPLIES 2
Anonymous
Not applicable

@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

 

5ac0a168-bda3-4e7e-9cef-7a7ef0781c67.png

 

amitchandak
Super User
Super User

@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.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.