Calculating Previous months 'max' value from start whenever customer changes in a calender year

0
votes

I am trying to calculate the previous months max value for 2 different customers in a calender year , where the calculation(max) should begin new whenever there is a change in customer. Hope the picture below will give more info :below is the mdx i am using.

case when([Time].[year-month-date].CURRENTMEMBER is [Time].[year-month-date].&[jan]) or [Measures].[Base Value]<>([TIME].[year-month-date].CURRENTMEMBER.prevmember,[Measures].[Base Value]) THEN null ELSE MAX(PERIODSTODATE([TIME].[year-month-date].[month],[TIME].[year-month- daenter image description herete].CURRENTMEMBER.PREVMEMBER),[Measures].[Sales]) END

**** BASE VALUE is just a number representing customer.

with this mdx i am getting the ”previous month” max value but i need to calculate ”previous months” max value . I have used the case statement to make the ’jan’ month always null and also whenever there is change in customer(base value)

can someone help me in getting it.

Kind Regards

Mah

enter image description here

1
ssasmdx

1 Answers

0
votes

Not sure if this’ll help as all I’ve done is tidy the syntax:

IIF(
([Time].[year-month-date].CURRENTMEMBER 
IS [Time].[year-month-date].&[jan]) 
OR
( [Measures].[Base Value]<>([TIME].[year-month-date].CURRENTMEMBER.prevmember,[Measures].[Base Value]) 
,NULL 
,MAX(PERIODSTODATE([TIME].[year-month-date].[month],[TIME].[year-month- daenter image description herete].CURRENTMEMBER.PREVMEMBER),[Measures].[Sales]) 
)