Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Turn Inventory transaction/movement table into inventory on hand total ie cumulative numbers

Hi,

 

I have a fairly large inventory transaction table that I need to convert into a inventory on hand table (by day).  Basically just date, item number, and quantity, with the quantity being the sum of inventory transactions all the way up to today.

 

In DAX I know you can do something like calculate (sum(quantity), filter(all(date), date <= max(date))

 

But the problem is it takes about 40 seconds for me to expand my heirachy in Power BI when I do that.  So I'm hoping to do some type of Power Query solution, but I'm stumped.

 

Any ideas?

 

Thanks,

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Ok, I figured it out. The problem was I had a filter on the visual that I only wanted to show total sales quantity > 0

     

    For whatever reason, that was REALLY dragging the visual down. I assume it was checking every row for quantity > 0 instead of just removing the items where the final total was zero from the report.

     

    Important lesson learned here. I’m loving the performance analyzer, and the ability to inspect the actual query.

     

    I will still put some effort into learning Power Query better as I imagine optizing this stuff will be important. 

     

    Thanks so much for the help

5 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    Look at this:

    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdFLDsMgDEXRvTCOFGwTPmuJMmi9/z20QSD5GQZMjizDTe470EknR47hCJ//oRqe41W2ylMFtA1NW71AZWjeatm+oW7f0ECvoRSB8+Sp33ex+DhQscrk4/rsEgcbslWJPg60wm3Nx/W9vMT1YfZxapVBi49T+4UTaPJxapMzKPm4rkucbuP6bFnidPy55wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Item = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Item", type text}, {"Quantity", Int64.Type}}),
    #"Added To Date Inventory" = 
        Table.AddColumn(
            #"Changed Type",
            "To Date Inventory", 
            each let 
                varItem = [Item],
                varDate = [Date]
            in
                List.Sum(
                    Table.SelectRows(#"Changed Type",each [Item] = varItem and [Date] <= varDate)[Quantity]
                ),
                Int64.Type
        )
in
    #"Added To Date Inventory"

     

     

    This returns this data

     

    Note too that this DAX code might be faster as it doesn't involve context switching by CALCULATE. Dunno. See if your matrix performs better with this. I don't have a date table here for this quick and dirty demo, and you should.

     

    DAX To Date = 
VAR varDate =
    MAX( Inventory[Date] )
VAR varItem =
    MAX( Inventory[Item] )
VAR Result =
    SUMX(
        FILTER(
            ALL( Inventory ),
            Inventory[Date] <= varDate
                && Inventory[Item] = varItem
        ),
        Inventory[Quantity]
    )
RETURN
    Result

     

     

     You can see my PBIX file here. If you need more help, please post back with some data per the links below.

    How to get good help fast. Help us help you.
    How to Get Your Question Answered Quickly
    How to provide sample data in the Power BI Forum

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      Thanks for the reply.

       

      I tried doing the Power Query thing, but it's going REALLY slow, on even a very very small subset of data (like 18,000 rows of wht is normally a 28 million row table).  Here is my code.  Note sure what I'm doing wrong

       

      let
    Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Posting Date", type datetime}, {"Item No_", Int64.Type}, {"Item Ledger Entry Quantity", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Posting Date", "Date"}, {"Item No_", "Item"}, {"Item Ledger Entry Quantity", "Quantity"}}),

#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns" ,{{"Date", type date}, {"Item", type text}, {"Quantity", Int64.Type}}),
    #"Added To Date Inventory" = 
        Table.AddColumn(
            #"Changed Type",
            "To Date Inventory", 
            each let 
                varItem = [Item],
                varDate = [Date]
            in
                List.Sum(
                    Table.SelectRows(#"Changed Type",each [Item] = varItem and [Date] <= varDate)[Quantity]
                ),
                Int64.Type
        )
in
    #"Added To Date Inventory"

       

      Here is a PBI file example of what I'm trying to do.  To clarify It's not actually the measures that get the inventory balance on hand that is really slowing things down, that runs super quick.  But it's when I take the average of the balance on hand that things get REALLY slow.

       

      So when doing this by measures I do

       

      Balance on Hand via Measure = CALCULATE(SUM('Inventory Transactions'[Quantity]), FILTER(ALL(Dates), Dates[Date] <= MAX(Dates[Date])))

       

      followed by

      Avg Bal on Hand Via Measure = AVERAGEX(VALUES(Dates[Date]), [Balance on Hand via Measure] )

      Which I do believe gives me the correct averages based on some Power Pivot verification.

       

      But as mentioned the averages run REALLY slow.

       

      So what I tried to do instead was use SummarizeColums to create an aggregated (and hopefully much smaller) version of the table.  Where the balance on hand calculations where already done.

       

      Inventory Summary = SUMMARIZECOLUMNS('Inventory Transactions'[Date] , 'Inventory Transactions'[Item No], 
"Inv Bal via Summarize", CALCULATE(SUM('Inventory Transactions'[Quantity]), FILTER(ALL('Inventory Transactions'[Date] ),'Inventory Transactions'[Date] <=MAX( 'Inventory Transactions'[Date] ) ) ) 

)

       

      Then I did an average

      Avg Bal on Hand Via Measure = AVERAGEX(VALUES(Items[Item No]), [Balance on Hand via Measure] )

      though that doesn't quite get me the correct answer (because the summarize columns is missing the weekend dates).  But it's much faster, at least on my desktop.  But I get a memory allocation error when trying to refresh on the service.

       

      Hopefully the above is more clearly, I should have written more carefully in the orginal post.  My apologies.

       

      Thanks again for the help,

       

       

      https://drive.google.com/file/d/1kytyZTT6k3qQSW-SJ2lym3PHGNlvzeed/view?usp=sharing

       

      • edhans's avatar
        edhans
        Community Champion
        6 years ago

        So for large data sets, Power Query will run slow because it is scanning the entire table. You can partition your data by item first using the techniques here via the Group By operation. The more you can partition your data, the better, so grouping by year and item for example.

         

        As for your DAX, you are using CALCULATE, and I would avoid that if possible. It does someting called context transition, and it can be expensive on a large table.

         

        Consider rewriting the first measure as this (I make no claims this works, I don't have data to validate, and I cannot see your model, and your google drive link requires a login.

        Balance on Hand via Measure =
VAR CurrentDate =
    MAX( Dates[Date] )
RETURN
    SUMX(
        FILTER(
            ALL( Dates ),
            Dates[Date] <= CurrentDate
        ),
        RELATED( 'Inventory Transactions'[Quantity] )
    )

        Then in the average, I would repeat that measure inside of it vs referring to it, as referring to it puts an implicit CALCULATE() around it.