How do deal with a while loop in Power Bi?

[Anjaneyachari]

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

[Anjaneyachari]

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

[amitchandak]

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