Performance when calculating Last Ever Non Empty in an Inventory Transaction Table

[Anonymous]

Hi,

We are currently struggling with performance when trying to apply a Last Ever Non Empty formula on a larger table in Power BI. We are using a live connection to a tabular model.

A simplified situation is as follows:
We have a table with these transactions:

 

The unique combination is Company + Warehouse + Product.

Since we only pull inventory changes, the inventory doesn’t change if a Product Quantity is blank the next coming days.

Our Requested output would be:

We would also like the inventory quantity to follow until the end of the Year, Month (depending on the filter)

We have a DAX-formula that is working:

CALCULATE(
   SUMX(
             VALUES('Inventory'[(Company/Warehouse/Product)Key]);
               CALCULATE(SUM(Inventory[ProductQuantity]);
                 CALCULATETABLE(
                    LASTNONBLANK('Date'[Date]; CALCULATE(COUNTROWS(Inventory)))
                                             )
                                  )
                  );
                     FILTER(ALL('Date'[Date]);'Date'[Date] <= MAX ('Date'[Date]))
                   )

We had a DATESBETWEEN after the CALCULATETABLE which gave the same results:

CALCULATE(
   SUMX(
             VALUES('Inventory'[(Company/Warehouse/Product)Key]);
               CALCULATE(SUM(Inventory[ProductQuantity]);
                 CALCULATETABLE(
                    LASTNONBLANK('Date'[Date]; CALCULATE(COUNTROWS(Inventory)))
                     ;DATESBETWEEN('Date'[Date];BLANK();MAX('Date'[Date]))
                                             )
                                  )
                  );
                     FILTER(ALL('Date'[Date]);'Date'[Date] <= MAX ('Date'[Date]))
                   )

When we are applying this on a specific Product the performance is good and the results are generated quite fast. However, when we want to apply this over all products we go out of memory.

Does anyone had a comparable situation that can provide with a workable solution with the performance issues? Or if you know how we can tweak the DAX-formula to help with the performance issues.

Dataset example: PBIX file

One table has a Productfilter and the other doesnt. The performance differs alot. Measures have been created. One iterates through a calculated column, one from a loaded - values are the same.



Best regards
Gustav

[Anonymous]

Dataset has been added: https://onedrive.live.com/authkey=%21AAvrjFChprYWRtg&id=E7970948A7180805%21313&cid=E7970948A7180805

[dwikar]

We're having the same issue / challenge - input would be really appreciated!

The classic LastNonEmpty with MOLAP / MDX over the same data set is performing well - a bit surprised to see that Tabular / DAX doesn't come with something similar OOTB.