Forum Discussion

sajal161292's avatar
sajal161292
Helper V
8 years ago

To compute some rows dynamically

Hi,

 

I am new to Power BI and stuck up while re-computing the value of a field in my report.

 

I need to adjust the value for the field On hand in the current week as per the previous week's cumulative total value(Demand+Inbound Supply+On Hand).

 

Please find the screenshots below.

 

As an example

Week 47:

On Hand = Total of previous week(46) = 557.72

 

Week 48:

On Hand = Total of previous week(47) = -993.83

 

Similarly for the other weeks as well.

 

Also as we are adjusting the value for on hand field then the cumulative total will also get updated accordingly.I need to implement this in one of my report very urgently.

 

Please find the link for the test file aslo : https://drive.google.com/drive/folders/12Ou3PcwDie0aju7ZV8ISSbeKsvXdZ6Vp

 

Any help will be greatly appreciated!!

 

Need Help

7 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi sajal161292,

     

    I'm afraid we can't do it with DAX. Let me make some explanations.

    1. Make it simpler - Total quantity of last week. We could do it this way.

    QuantityOfLastweek =
CALCULATE (
    SUM ( 'ShortageData'[Quantity] ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Week] = 'Calendar'[Week] - 1 )
)

    2. Your scenario. 

    Week 47 of QuantityOfLastweek = Week 46 of (Demand + Inbound Supply + On Hand). 

    The problem is no formula for Week 46 of On Hand. Because it depends on Week 45 and recurs endlessly.

     

    Maybe I missed some conditions. Do you mind adding a new order type "Total"? This could be helpful.

     

    Best Regards!

    Dale

      • sajal161292's avatar
        sajal161292
        Helper V
        8 years ago

        Hi,

         

        Additionally I would like to ask if there is any way to modify the query instead of writing up DAX expression for representing this calculation in the report.