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

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

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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