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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors