More effcient formula- most recent cost update starting and ending date

[Drobinson1]

This one may be complicated to explain, but I will give it a shot. I have three tables:

Table1=Recipe Table

Table2= Date Table

Table3=stdcosthistory table

There is no relationship for these 3 due to other complications within the reports and measures.

Recipe Table contains a material number and a warehouse number.

Stdcost history shows all materials and warehouse cost changes with a valid from date.

Date table is a standard date table that acts as an independent slicer on the report.

I want to add a measure in the recipe table that based on product and warehouse and also the date range selected in the date filter table, it would pull in the most begging cost and ending cost during that date range.

Example:

Recipe Table

Product       Warehouse 

0230                01

Date Slicer filter= From 1/1/2015 to 8/19/2016

Cost table

Product         Warehouse        Cost      Valid From

0230                     01                  .25         12/20/2012

0230                     01                  .35          12/20/2014

0230                     01                  .15           1/15/2015

0230                     01                  .26            7/8/2016

0230                     01                  .29            12/1/2016

 I have a few measures in the recipe table however they seem to take up to much memory most of the time.

First two find the min and max date of the selected date range.

minddate = MIN(RC_Daily_Sales_Report_Invoice_Line_Detail[Invoice_Date])

maxdate = MAX(RC_Daily_Sales_Report_Invoice_Line_Detail[Invoice_Date])

These are the final two formulas that seem to work but eat up to much memory.

AvgBegStdCost =
CALCULATE(AVERAGE(IC_STD_COST_HISTORY[CURRENT_STD_COST]),FILTER(IC_STD_COST_HISTORY,IC_STD_COST_HISTORY[VALID_FROM_DATE]=[stdcostfirstdate]),FILTER(IC_STD_COST_HISTORY,IC_STD_COST_HISTORY[PART_CODE]=[RawMaterialPartcode]),FILTER(IC_STD_COST_HISTORY,IC_STD_COST_HISTORY[WAREHOUSE]=VALUES(RC_FULL_LEVEL_RECIPES[Warehouse_0])))

AvgEndStdCost =
CALCULATE(AVERAGE(IC_STD_COST_HISTORY[CURRENT_STD_COST]),FILTER(IC_STD_COST_HISTORY,IC_STD_COST_HISTORY[VALID_FROM_DATE]=CALCULATE(MAX(IC_STD_COST_HISTORY[VALID_FROM_DATE]),FILTER(IC_STD_COST_HISTORY,IC_STD_COST_HISTORY[VALID_FROM_DATE]<[maxdate]),FILTER(IC_STD_COST_HISTORY,IC_STD_COST_HISTORY[PART_CODE]=VALUES(RC_FULL_LEVEL_RECIPES[RawMaterial])),FILTER(IC_STD_COST_HISTORY,IC_STD_COST_HISTORY[WAREHOUSE]=VALUES(RC_FULL_LEVEL_RECIPES[Warehouse_0])))),FILTER(IC_STD_COST_HISTORY,IC_STD_COST_HISTORY[PART_CODE]=[RawMaterialPartcode]))

 Any Ideas?

[AlexChen]

Hi,

I can see that there are too many separated filter expressions in your formula AvgBegStdCost  and AvgEndStdCost. Each FILTER() is a iterator which will iterate entire table so that it consumes memory.

Since your FILTER() expression is on same table, please put your conditions into one filter expressions.

For example,

AvgBegStdCost =
CALCULATE(AVERAGE(IC_STD_COST_HISTORY[CURRENT_STD_COST]),
          FILTER(IC_STD_COST_HISTORY,
                       IC_STD_COST_HISTORY[VALID_FROM_DATE]=[stdcostfirstdate] &&
                       IC_STD_COST_HISTORY[PART_CODE]=[RawMaterialPartcode] &&
                       IC_STD_COST_HISTORY[WAREHOUSE]=VALUES(RC_FULL_LEVEL_RECIPES[Warehouse_0]))
          )

Best Regards
Alex

[MattAllington]

I am going to give you my opinion here - others may not agree 🙂  

I help a lot of people with DAX problems. One problem I see is that you are not following what I think is best practice in preparing your data.  I would not even attempt to look at your formulas becuase they are far too confusing to me.  You may understand what you are doing, but the fact you are stuck suggests there is an easier way.  This is what I recommend.

1. Change your table names to something simple.  How about "Calendar", "Recipie" and "Cost"

2.  Rename your columns to something simple.  eg Cost[From Date], Cost[Current Cost] etc

Next, I can't tell if you should have relationships or not.  Sometimes if you defer solving a problem in one stage, you just make it harder in the next stage.  I couldn't say for sure.  Is it a many to many problem?  If so, you could read my article here http://exceleratorbi.com.au/many-many-relationships-dax-explained/

What are the stats of your tables?  How many rows, how many columns?

I assume you are using 32 bit.  Can you move to 64 bit?