Measure not working as desired

[Anonymous]

Hi all,

I have the below measure with set analysis:

SUM({<[CloseDate]={'>=$(vMaxDateLastMonthStart)<=$(vMaxDateLastMonthEnd)'}>}Sales)

vMaxDateLastMonthStart: =date(num(Monthstart(MonthStart(max(CloseDate))-1)))

vMaxDateLastMonthEnd: =date(num(MonthStart(max(CloseDate))-1))

The measure works fine when I select this month but when I select last month, the measure becomes zero.

The date variables however are fine. I.e. when I select say July, vMaxDateLastMonthStart shows June 1st & vMaxDateLastMonthEnd shows June 30th.

Is there anything wrong with my set analysis? How come it works only for the current month but the previous months become zero.

Regards,

SS.

[sunny_talwar]

May be you just need to ignore selection in Month field?

Sum({<[CloseDate]={'>=$(vMaxDateLastMonthStart)<=$(vMaxDateLastMonthEnd)'}, Month, Year>}Sales)

[Anonymous]

Ah no, I want them to work.

If I have selected June then the measure should display sales of May.

Any ideas how my date variables are working but my measure says a big fat zero?

[sunny_talwar · Accepted solution]

It will work... your month selection will drive Max(Close Date) which will show you the result you want to see.... but the problem is that as soon as you select a month, the other months zero out.... that is why you need to ignore Month and probably the year field.... give it a shot and see if it doesn't work

[Anonymous]

Perfect thanks.