Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all
I want to create a measure that gives me the inventory value with following dimensions in a pivot table.
Top Dimension:YearMonth,YearWeek
Measure:Inventory for the week.
Not a problem for the week but for the Month Total I want to display the max week for that months value. I have tried with Last, Max, IF statement etc but cant figure it out.
Any suggestions? MaxWeek Inventory is the desired result.
Year Month Base (SCM) | 20M01 | 20M02 | |||||||||
Year Week Base (SCM) | Total | 20W01 | 20W02 | 20W03 | 20W04 | 20W05 | Total | 20W06 | 20W07 | 20W08 | 20W09 |
Inventory | 107 655 | 21 430 | 20 957 | 21 313 | 21 742 | 22 213 | 89 294 | 22 378 | 22 824 | 21 331 | 22 761 |
MaxWeek | 20W05 | 20W01 | 20W02 | 20W03 | 20W04 | 20W05 | 20W09 | 20W06 | 20W07 | 20W08 | 20W09 |
MaxWeek Inventory | 22 213 | 21 430 | 20 957 | 21 313 | 21 742 | 22 213 | 22 761 | 22 378 | 22 824 | 21 331 | 22 761 |
the last week of the month will not change. i suggest in your data model add a flag for the last week and use that flag in your expression. that will be easier than a complex expression
Hi Edwin,
The desired solution is a pivot table with first months and then weeks. I want a total on Weeks which should be the max weeks value of the month. How would a flag work? I understand the concept of flagging the last week of the month but how will I use that just for the total function of the weeks for the month?
Thanks
The flag will allow you to create expressions targeting just the last weeks of the month; it will also give the ability to apply expressions for weeks that arent the last week of the month. see attached