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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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