To calculate the total dynamically

Hi,

I may be able to solve this in Ecel using the PowerPivot and CUBE functions.  I cannot solve this in PBI desktop.  Sorry but will not be able to help.

Thanks for your help and time!!

Hi Anonymous

Thank you for the update!!

Were you able to find a solution to this problem with the pbi file shared in one of the trailing posts?

I am very new to Power BI and need to implement it in one of my reports urgently.

I have modified my pbi file with some calculations and shared it below:

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

Now i need to make some changes in the way data is being represented in the file.I need to re-adjust the value of on hand quantity of the current week with the cumulative total of the previous week(demand+inbound supply+on Hand).Presently the on hand field is showing the same value for every week(in this example)

For week 47:

On hand = running total of previous week(46)= 557.72

For week 48:

On hand =  running total of previous week(46)=-993.83 etc.. till the last week.

Adjusting the value of on hand will also change the cumulative total that is being represented for any week.

I need to implement this in one of my reports very urgently as it is hampering the progress of it.

Any help/pointers will be greatly appreciated!!

Please let me know for any question/clarification

Thanks

Hi sajal161292,

I think you can add filter to check where the formula is calculated on and use previous result to replace current value.(your visual contains complex grouping columns, it is hard to write a specific condition to filter on it)

For example:

Modify formula shows total as 0, May,July shows 'custom'.

Regards,

Xiaoxin Sheng

Hi Anonymous,

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

Additionally i have added a new order type as Total.

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!!

HI sajal161292,

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

It is possible to achieve this, but you need to add huge amount of conditions to help measures confirm where current content calculated. 

In my opinion, power bi not support auto/dynamic calculate on complex measures(exist some specific filters, contains break row contents function e.g. 'all/allexcept').

Notice: if your data contains any privacy data, please do mask sensitive data before sharing.

Regards,

Xiaoxin Sheng

Hi,

So will it be possible to perform this operation in DAX?

Thanks

Har**bleep**a Singh