Using last week's total to calculate this week's total

[mdhenzel]

Hello everyone

I think I may be thinking too much about this, but I need to do a race measurement to calculate a total. Please see the explanation below.

I have a calendar/date table built, along with Measures that add up all the necessary fields.

What I intend to do is calculate the sum total. For Min Week, it is Available Quantity (Monday) + Stock - Demand. For every week in the future, I need to take the "Sum Total" from the previous week plus the stock/demand difference from that week. So week 49 would be -793, week 50 is -4793 and so on. Please help!

[v-eqin-msft]

Hi @mdhenzel ,

Thanks for your feedback!😀

Please kindly Accept it as the solution to make the thread closed. More people will benefit from it.

Best Regards,
Eyelyn Qin

[mdhenzel · Accepted solution]

Hi All,

I have solved the problem. Below is the measure I used. Thank you all for the help!!!

Total Reach - Confirmed Shipments =

Var First = 'Reach Data'[Avail_Qty _Monday]

VAR LastVisibleDate =

MAX ( 'Date Table'[Date] )

VAR FirstVisibleDate =

MIN ( 'Date Table'[Date] )

VAR LastDateWithSales =

CALCULATE (

MAX ( 'Reach Data Breakdown'[Date] ),

REMOVEFILTERS ()

)

VAR Result =

First +

IF (

FirstVisibleDate <= LastDateWithSales,

CALCULATE (

[This Week SIT + DEMAND],

'Date Table'[Date] <= LastVisibleDate

)

)

RETURN

Result

[v-eqin-msft]

Hi @mdhenzel ,

As @lbendlin suggested, please try:

Measure= SUMX(FILTER(ALL('Table'), [WeekNumber]<=MAX('TABLE'[WeekNumber])),[Demand])+
SUMX(FILTER(ALL('Table'), [WeekNumber]<=MAX('TABLE'[WeekNumber])),[Stock])+
[Available Quantity]

If it isn't your expected, please provide more details about your table or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mdhenzel]

Hi @v-eqin-msft, I did attempt to make a measure using your guidance, but I came out with a very large number. Again.. For the minimum week, I would need available qty + difference in Stock in Transit and demand (i have a measure for the difference that can be used). All weeks not the minimum would take the previous weeks total + the difference in stock in transit and Demand. Please feel free to go into my pbix I shared in the previous comment. "This week SiT + Demand" is the measure I mention in this comment. Could a summary table be a good idea?

[lbendlin]

I slightly rearranged your data model. Please provide a rationale for the tables highlighted in yellow.

[mdhenzel]

"Reach Inputs" is a Microsoft List controlled by an app I have yet to put in the file. It is where the inputs for Stock in Transit will be inputted weekly by my material planners. "Month Table" and "Day of Week" just allow me to pull in the name of the month or day. "Current Day Available" is a summary table to get my Available stock for Monday. I did it this way to summarize the total by the "plant summary", as there is more than one plant per plant summary. I am still a rookie/moderate Bi user, so I seperate everything out then pull the data back in. 

[mdhenzel]

Hi @v-eqin-msft , Here is a link to the file. I changed any indicators that would tip you off to who I work for. I haven't tried your solution yet, but will after my all day training. Thank you for the reply!

https://drive.google.com/file/d/1x_Eoy0YWhUWedsYFbLX7Jhf_aas36Wma/view?usp=sharing

[lbendlin]

You would use SUMX for that.

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[mdhenzel]

Hi Ibendlin, Below is the link to the file. I took out any key indicators. Hope this can help you to help me. Thank you for the reply. 

https://drive.google.com/file/d/1x_Eoy0YWhUWedsYFbLX7Jhf_aas36Wma/view?usp=sharing