cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Calculate stock holding at last available week and 4 weeks prior

I am trying to get part of my report to show the stock holding at the last available date [W.E. Date] and 4 weeks prior to that.

I have a sum of the sales over each of the 4 week periods but stock must be closing stock at the end of each of the 4 weeks - it cannot be summed - averaging is not cool either.

I have a column call 8Wks which works over the last 8 weeks and shows "Last 4 weeks" if [W.E. Date] is within the last 4 dates in the file or "Previous 4Wks" if [W.E. Date] is within the last 5 to 8 weeks in the file. Any other dates outside of this = NULL. So 8Wks has 3 values "Last 4 weeks", "Previous 4Wks", NULL

Getting the sum of the [SALES] therefore is easy, however I now need to get:

the sum of the [STOCK] at the max([W.E. Date]) where [8Wks] = "Last 4Wks"

and

the sum of the [STOCK] at the max([W.E. Date]) where [8Wks] = "Previous 4Wks"

in order to show the below matrix

 Period Sales Stock WOS Last 4Wks 100 474 4 Previous 4Wks 131 508 3

WOS weeks of stock I should be able to manage!

4 REPLIES 4
Community Support Team

## Re: Calculate stock holding at last available week and 4 weeks prior

Hi @LUCASM

I'm afraid i cannot fully understand it. Could you share some simplified sample data?Please refer to You can also upload the .pbix file to OneDrive and post the link here or send me via private message. Do mask sensitive data before uploading.Below is the post about WOW for your reference.

https://community.powerbi.com/t5/Desktop/Calculate-Week-over-Week-change-based-on-Number-of-weeks-si...

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Calculate stock holding at last available week and 4 weeks prior

Hi All

Here is a link to a demo of what I am trying to achieve

PBIX file

The table I need to correct is the bottom one I have highlighted the expected figures in the table above, I hope that makes more sense now.

Community Support Team

## Re: Calculate stock holding at last available week and 4 weeks prior

Hi @LUCASM

It seems you may create a calculated column to get the week column.For example:

```Column =
IF (
Sheet1[W.E. Date] <= MAX ( Sheet1[W.E. Date] )
&& Sheet1[W.E. Date]
> MAX ( Sheet1[W.E. Date] ) - 4 * 7,
"Last 4Wks",
IF (
Sheet1[W.E. Date]
<= MAX ( Sheet1[W.E. Date] ) - 4 * 7
&& Sheet1[W.E. Date]
>= MAX ( Sheet1[W.E. Date] ) - 8 * 7,
"Previous 4Wks"
)
)
```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Calculate stock holding at last available week and 4 weeks prior

Hi Cherie Many thanks for your solution, it does not appear to work though as it brings up the following message:

A single value for column 'W.E.Date' in table Sheet1 cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggrigation such as min, max, count or sum to get a single result.

That said on working through it I thought, can I do this at the server end and put a flag against each of dates in question in a similar formula that you have used here and then add it to the Visual level filters

It worked!!!

If it were not for your solution I would probably not have tried this so thanks for the time you invested.

Martin