Last Three Months Calc

[#1]

Hi,

I am creating a Pivot Table chart which will show certain Calculation the last three months.

This report will be static, say if we are in the Month of - FEB then the Report should show NOV-DEC-JAN

Nov	Dec	Jan

In the next month ie. MAR the Report should show DEC-JAN-FEB.

Dec	Jan	Feb

The issue I am facing is with the Year, since it is 2014 and months needed in the report are of 2013 as well, I am stuck !

Please help me on the same.

Thanks.

[#2]

=sum({<DateField={'>=$(Vrolling3)<=$(vMaxDate)'},monthfield=,yearfield=>}Amount)

Variables:

Vrolling3 : =Date(MonthStart(vMaxDate,-2),'DD/MM/YYYY')

vMaxDate : =Date(max(Date_Field),'DD/MM/YYYY')

[er_mohit · Accepted solution]

See the attached file

[#4]

Hi

=(Count({<[Year ]={$(VarPrevMonthFinYear)},[ Month]={$(VarPrevFinMonth)}>}MeasureField)

Below is Variables description:

VarPrevFinMonth=Month(AddMonths(VarFinMaxDate,-1))

VarFinMaxDate=day(max({<[Year ]={$(VarFinYear)},[MonthNum]={'$(VarFinMaxMonth)'}>}[Date]))

VarFinYear=max([Year])

VarFinMaxMonth=MAX({<[Year]={$(VarFinYear)}>} [MonthNum])

Hope it Helps you

Regards,

Divya

[kiranmanoharrode]

Hello Priyanka,

You must have Month and MonthNum field in your table

For Ex. Month ='Jan' ; MonthNum= '1' ; Month='Dec'; MonthNum='12';

add calculated Dimension in your chart for displaying Month

=if(Today()=Today(), >= Month(addMonths(Today(),-3)) and <= Month(addmonths(Today(),-1)))

Regards

Kiran Rode

8976977897

[MK_QSL]

Create a Pivot Table/Straight Table...

Dimensions As per your requirements.

Considering that your Month are like 'Jan', 'Feb' etc...

Current Month

Label : =Month(Today())

Expression : SUM({<Month = {'$(=Month(Today()))'}>}Sales)

Previous Month

Label : =Date(AddMonths(Today(),-1),'MMM')

Expression : SUM({<Month = {"$(=Date(AddMonths(Today(),-1),'MMM'))"}>}Sales)

2Months Back

Label : =Date(AddMonths(Today(),-2),'MMM')

Expression : SUM({<Month = {"$(=Date(AddMonths(Today(),-2),'MMM'))"}>}Sales)

And SO ON... Hope This helps..

If you want to compare as MonthNumber... you can simply use

MonthNo = {"$(=AddMonths(Today(),-1))"}

Instead of

Month = {"$(=Date(AddMonths(Today(),-1),'MMM'))"}