6 Replies Latest reply on Mar 7, 2019 9:19 AM by Tim Vanstraten

show the last 3 months data from the month period to REPORTING MONTH(this month-1)

Hi,

I have a requirement to show 3 months  data for a particular measure. Here I am can show only the 3 months which are prior to reporting month(this month-1). Ex:- for this month the reporting month will be SEPT. so I have to show the SUM(Measure value) for agreegated value for JUNE,JULY,AUG. I am succesfuuly able to get the data using this formula in the calc. field

month(TODAY())-MONTH([DATE])<5 and year(today())-YEAR([DATE])=0

and month(TODAY())-MONTH([DATE])>=2

But this formula won't work when I will be running this report in JANUARY,2017 or subsequent months when we have data from last year. I had tried with LAST() but I won't be using the DATE field in the sheet itself

I want a calculated field which should automatically change the data whenever the month and year changes and show be me data for the 3months from REPORTING MONTH-1 month

Please find attached the sample workbook file here for reference

• 1. Re: show the last 3 months data from the month period to REPORTING MONTH(this month-1)

Hi Animesh,

please write the below formula and drop to filter shelf

LAST()>=1 and LAST() <= 3

1 of 1 people found this helpful
• 2. Re: show the last 3 months data from the month period to REPORTING MONTH(this month-1)

But i am not showing the Date field itself in the table hence the above formula won't show any data. I had mentiond that in the question itself

thanks again

• 3. Re: show the last 3 months data from the month period to REPORTING MONTH(this month-1)

Hi,

Can you use the below calculation which will help you to have values even if you are moving ahead to a new year

Calculation

DATEDIFF('month',[DATE],[today])<=3 and DATEDIFF('month',[DATE],[today])>=1

today =TODAY()

DATE = Database filed.

Use this calculation in the filter which only fetches the last 3 months.

1 of 2 people found this helpful
• 4. Re: show the last 3 months data from the month period to REPORTING MONTH(this month-1)

Hi AJ,

thanks for the response

I suspect that this formula won't work when I run this report in JAN,2017. I ran the calculated field provided by you with the [today] calc. field which ahs Jan,2017 date and it didn't work. this is the same reason why I had taken the today as a calc. field

thanks

Animesh

• 5. Re: show the last 3 months data from the month period to REPORTING MONTH(this month-1)

Hi Animesh ,

I am also stuck with same issue.

I need to use 3 months data i.e. Current Month-1 in One calculation .

I am using MONTH([Month])=MONTH(TODAY())-1 ,MONTH([Month])=MONTH(TODAY())-2 ,MONTH([Month])=MONTH(TODAY())-3 to take last 3 months data. Here Month is data field.

But on Year change it is not working.

Could you please suggest any workaround.

Thanks

Mausami

• 6. Re: show the last 3 months data from the month period to REPORTING MONTH(this month-1)

I have been trying to do this as well.  The issue seems to be I cannot create a range of dates based off the max date of the date filter in the view.

I likewise want to show the past 3 months of a Measure where my anchor date is actually the max date that gets set by the user with a dashboard date range slider.  This is possible when using a window max or a datediff with filter as show above, but I only want to show the measure in my view, and not the extra columns.  To actually get this to work based of a dynamic anchor date seems impossible.

Any help?