Calculate stock holding at last available week and 4 weeks prior

[LUCASM]

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! 

[v-cherch-msft]

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

[v-cherch-msft]

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

[LUCASM]

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

[LUCASM]

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.