Get previous day data problem and support needed!

[NBU_FFF]

Dear Community,

I stuck in a challenging task by getting previous day for next day calculation.

In below table,  I need to use measure to get prevoius date Balance for next day ini and use that for next day Balance calculation.

The logic for Balance:

Balance Calculation for line A=(Line A Ini+Line A Production)-(Line A Ini+Line A Production)/(Total Ini+Total Production)*Capacity

Balance Calculation for line B=(Line B Ini+Line B Production)-(Line B Ini+Line B Production)/(Total Ini+Total Production)*Capacity

Capacity: 10

I'm thinking on that solution for a week but could not settle it donw especially in dealing with those in 0 for productoin, could anyone support on that by advise a correct dax measure?

A Big Thanks!

[v-saisrao-msft]

Hi @NBU_FFF ,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[lbendlin]

Seems wasteful to repeat the same numbers. What would you lose if you would drop the "Ini" column?

[NBU_FFF]

@lbendlin ， Thanks for reply, actually "Ini" is the temp variant. We can drop it so long as the "Balance" could be made per my logic above. It seemed easy in Excel but hard to figure out in PBI.  

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[NBU_FFF]

@lbendlin  Pls find my sample data and excel logic https://1drv.ms/f/s!AhkI23dIWO7ygrpXDlKv9uTZro6QMg?e=INk7dN 

Now I'm able to make a DAX to fulfill the logic in sub-sum (Value Stream) level, but from second day the line level data still not correct. pls have a good and advise if you could figure it out. Thanks.

[lbendlin]

your Calendar table is not suitable

Your data model needs to be adjusted

Please explain how the capacity plays into the formula.

[NBU_FFF]

@lbendlin ,thanks for your reply. Acutally my calendar table is the customized one which need to remove some special dates. Pls kindly refer to my original one. 

Also for the capacity, it calculated in measure ini. e.g. Mar.21 AC01=(Backlog+Mav)- (Backlog+MAV)/Total* Capacity of this value stream= 397365-397365/616242*80000=345779

Hope it clarifies. Pls kindly let me know if more information you need. Thanks again.

[NBU_FFF]

@lbendlin Inspired by your comments now I have solved part of the problem. I used calculated columns to get the data but from my solution seemed I have to create multiple colomns which I beleive not the best solution. Could you pls have look and advise a potential way out?

I posted a new topic for this question.

Repeated Calculated Column Optimization - Microsoft Fabric Community

Thanks.

[v-saisrao-msft]

Hi @NBU_FFF,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @NBU_FFF,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.