Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!!
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
Hi,
Thank you for the reply!!
1.I have added the measure QuantityOfLastWeek in the file but is not showing any value in the report.
2.I have added the Total order type in the file.
Please find the updated pbi file in the link
https://drive.google.com/drive/folders/12Ou3PcwDie0aju7ZV8ISSbeKsvXdZ6Vp
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.
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!!
Hi,
Can anyone please help me with this?
I am really stuck up on it,have tried to implement this functionality in sql but it has become a way too complex.
I need this really badly.
Hi @sajal161292,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
Dale
Hi @sajal161292,
Sorry so late. Too much work to do. I found a workaround though it isn't prefect. You can try it and may get some ideas. You can test it in this file.
Measure = CALCULATE ( SUMX ( SUMMARIZE ( '0ShortageData', '0ShortageData'[Org], '0ShortageData'[Part], '0Calendar'[Week], "value", SUM ( '0ShortageData'[Quantity] ) ), [value] ), ALL ( '0ShortageData'[OrderType (groups)] ), FILTER ( ALL ( '0Calendar' ), '0Calendar'[Week] < MAX ( '0Calendar'[Week] ) ) )
Best Regards,
Dale
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |