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 ...
Need Help
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.

       

      • sajal161292's avatar
        sajal161292
        Helper V
        8 years ago

        Hi,

         

        I have modified my pbi file with adding up a new measure for representing the previous week sales.

         

        Now i am stuck up while re-assigning the value of previous week sales to the field Total.

        Is there any way to perform iteration in DAX and traverse for each record?

         

        Please find the link of new pbi file below

         

        https://drive.google.com/drive/folders/12Ou3PcwDie0aju7ZV8ISSbeKsvXdZ6Vp

         

         

        Thanks for your help!!