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

Power Query - Nested loop iteration with the output in the first row and so on

Hi All, 

 

I'm trying to use Power Query instead of VBA to do a WIP days calculation. I'm struggling to work out whether I can do the following and what the most efficent way would be as there could be many lines. I'll provide a quick example below to show you what I'm after.

 

I would like to do a calculation on each row where I take the Fees away from a running WIP figure until it is less than or equal to zero. Once it is, I want the Running WIP and number of months to output on the first row and then do the same calculation on the second row and so on. I'll give two illustrations below.

 

Jan-22 Row

 

Running WIP = WIP - First Row Fees = 100 - 50 = 50 (Greater than 0, Count = 1)

Running WIP =Running WIP - Dec-21 Fees = 50 - 60 = -10 (Less than 0, Count = 2)

Output in row 1. # of months = 2, Running WIP = -10

 

Dec-21 Row

 

Running WIP = WIP - Second Row Fees = 150 - 60 = 90 (Greater than 0, Count = 1)

Running WIP = Running WIP - Nov-21 Fees = 90 - 45 = 45 (Greater than 0, Count = 2)

Running WIP = Running WIP - Oct-21 Fees = 45 - 80 = -35 (Less than 0, Count = 3)

Output in row 2. # of months = 3, Running WIP = -35

 

jdodkins_0-1644835985756.png

 

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @Anonymous 

 

Do you must do it with Power Query? If you may accept DAX solutions, you can try my method below. If you want to do it only with Power Query, let me know then I will try to work it out. 

 

First add a custom column with Power Query to get Month Start Date for every row. 

Date.FromText("1"&[Month])

vjingzhang_0-1645081613948.png

 

After applying the change to data model, create calculated columns with the following DAX formula. 

Month WIP used in = 
VAR __table =
    SELECTCOLUMNS (
        FILTER ( 'Table', 'Table'[YearMonth] <= EARLIER ( 'Table'[YearMonth] ) ),
        "_yearMonth", 'Table'[YearMonth],
        "_WIP", 'Table'[WIP],
        "_fees", 'Table'[Fees]
    )
VAR __firstWIP = 'Table'[WIP]
VAR __table2 =
    ADDCOLUMNS (
        __table,
        "_runningWIP",
            __firstWIP
                - SUMX ( FILTER ( __table, [_yearMonth] >= EARLIER ( [_yearMonth] ) ), [_fees] )
    )
RETURN
    MAXX ( FILTER ( __table2, [_runningWIP] <= 0 ), [_yearMonth] )
# of months = DATEDIFF ( 'Table'[Month WIP used in], 'Table'[YearMonth], MONTH ) + 1
Running WIP = 
'Table'[WIP]
    - SUMX (
        FILTER (
            'Table',
            'Table'[YearMonth] <= EARLIER ( 'Table'[YearMonth] )
                && 'Table'[YearMonth] >= EARLIER ( 'Table'[Month WIP used in] )
        ),
        'Table'[Fees]
    )

vjingzhang_1-1645082067572.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Anonymous
Not applicable

Hi Jing,

 

Thank you so much for taking the time to look at this, I really appreciate it and the solution works great!

 

The only reason why I was concerned to use DAX instead of power query was a concern that it would have performance issues if I was needing to iterate over many rows of data. The example I provided was a small illustration but I may need to summarise and performance this calculation across a dataset that is initially thousands of rows before being transformed. 

 

From your experience, would you expect the DAX solution to slow down performance on a large dataset?


Thank you again.

 

James

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.

Top Solution Authors