cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
LUCASM Regular Visitor
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
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 How to Get Your Question Answered Quickly.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.
LUCASM Regular Visitor
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
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.
LUCASM Regular Visitor
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