Forum Discussion

fatconductor's avatar
fatconductor
Frequent Visitor
4 years ago
Solved

Continue last result in calculation

Hi there,  I feel like I'm 80% there with this solution but just need a little bit more help. I'm looking to project my forward inventory, based on the last available data, into the future. The...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi fatconductor ,

    Please have a try.

    Create a measure.

    Measure_1 =
CALCULATE (
    SUM ( 'Test Data'[VALUE] ),
    FILTER (
        ALL ( 'Test Data' ),
        'Test Data'[VERSION] = "Actual/Forecast"
            && 'Test Data'[FIN_WEEK] = SELECTEDVALUE ( 'Test Data'[FIN_WEEK] )
            && 'Test Data'[MEASURE] = SELECTEDVALUE ( 'Test Data'[MEASURE] )
    )
)

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

Power BI has no memory, and no global variables.  You need to solve this issue by using an aggregator function, specifically SUMX()  that computes your inventory movements from a base date to the current filter context. For every data point!

 

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Thank you for providing the sample data. That helps a lot with proposing a potential solution.

     

    "Projected SOH = Last weeks Network SOH - Adjusted FC + On Order"

     

    Which part of your sample data is the Adjusted Forecast?

    • fatconductor's avatar
      fatconductor
      Frequent Visitor
      4 years ago

      Hi Ibendin,

      In the sample data there are categorical values for "Units Sold". In the past they are sales and in the future it is forecast they are one and the same.

      Adjusted FC is an % increase/decrease slider linked to "Units Sold" in the future, allowing the users to adjust the forecast by %'s and see the implications to $$ spend and (hopefully) Projected SOH.

      It is a nice to have in this solution, but not imperative. I can always try to hack it on later. 

      "Projected SOH = Last weeks Network SOH - Units Sold + On Order" is also fine.

       

      Thanks for your help