Last Weeks value in pivot table with Month/Week

[christian_hallq]

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

[edwin]

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

[christian_hallq]

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

[edwin]

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