Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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.
Regards,
Cherie
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
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
Hi All
Here is a link to a demo of what I am trying to achieve
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |