Forum Discussion

zeckert's avatar
zeckert
Icon for Helper I rankHelper I
8 years ago

Measures Calculating over 52 weeks

Currently i have Measures Calculating Production over the next 52 weeks based on 90 day average of current production.  I have something to determine how far along the project to determine whether production would double or triple.  I also have field to determine whether there is enough inventory for the production to continue.  the problem i'm having is that the measure are too memory intensive and is causing bi not to be able to produce past week 6.  All the data is pulling from a excel sheet that brings in each line item of production.

 

I need to simplify this so i can produce the 52 week period and be able to show current inventory based on the forecast.

 

This is my first week.  each week after changes the back log to calculate with the inventory of previous week

Week 01 Production =
var MaturityTO = AVERAGE('Start Times'[First Job Date])<=([Current Monday]-30)
var MaturityTrim = AVERAGE('Start Times'[First Job Date])<=([Current Monday]-152)

 

03 Inventory = [02 Inventory]-[Week 03 Production]
var backlog = [Total Phases]
return
IF(MaturityTO,
IF(MaturityTrim,
IF(backlog<[Trim Calc],backlog,([Trim Calc])),
IF(backlog<[TO Calc],backlog,[TO Calc])),
if(backlog<[Average 90 Days],backlog,[Average 90 Days]))

 

Week 02 Production =
var i = 7*1
var MaturityTO = AVERAGE('Start Times'[First Job Date])<=([Current Monday]+i-30)
var MaturityTrim = AVERAGE('Start Times'[First Job Date])<=([Current Monday]+i-152)
var backlog = 'Inventory'[01 Inventory]
return
IF(MaturityTO,
IF(MaturityTrim,
IF(backlog<[Trim Calc],backlog,([Trim Calc])),
IF(backlog<[TO Calc],backlog,[TO Calc])),
if(backlog<[Average 90 Days],backlog,[Average 90 Days]))

Need Help
Tips and Tricks

4 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi zeckert,

     

    To simplify your code, you can use function like filter(table, DATEADD(Calendar[Date], -7, DAY)) to achieve value a week ago instead of use variable to save value in current date. You can also use "switch" to replace "if".

     

    Regards,

    Jimmy Tao

     

    • zeckert's avatar
      zeckert
      Icon for Helper I rankHelper I
      8 years ago

      The switch function does not allow me to do comparison true/false.

    • zeckert's avatar
      zeckert
      Icon for Helper I rankHelper I
      8 years ago

      it seems the biggest problem with the formula is needing to check the ongoing inventory to see if there is enough.  Once i start building the inventory this is when it breaks.

    • zeckert's avatar
      zeckert
      Icon for Helper I rankHelper I
      8 years ago

      it seems the biggest problem with the formula is needing to check the ongoing inventory to see if there is enough.  Once i start building the inventory this is when it breaks.

       

      Even with Switch i think because its going through several measures it eventually breaks.  I need to find a way to either combine both inventory and production into two separate measures that can be duplicated over a set number of time or utilize calculated columns without circular dependency errors.