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

[Anonymous]

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

[v-jingzhang]

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])

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]
    )

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

[Anonymous]

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