Announcements
cancel
Showing results for
Did you mean:
Contributor III

## How to get Last 3 Months sales data for each date dynamically (excluding Current month)

Hi Guys,

I have Date as dimension in  bar chart (presing latest  4 months date) and measure as sum(sales)..

Now i have to add another measure  to find sum(sales) for last 3 months dynamically excluding current month.. for each date wise going back for last 3 months of Sum(sales)?

after that i have to  calculate AVg sales -->   current date(sales)/last 3 months sales

sample data below  having  dates from March, April, may, june month dates..

Labels (3)

• ### General Question

17 Replies
Contributor III
Author

Could you please respond any one for this post..

giving more details...

If  my date is  may-18 --> then  i have to get sum(sales) for  FEb, March, APRil month   later to find avg for last 3 months

if my dimesion date showing June-2 Means -->    have to get sum(sales) for Mar,Apr, May  Months

after finding avg for last 3 months (excluding current month dates)like this ..  and  i have to calculate  like below

--> June2(sales) / 3 months (mar,Apr,May)avg sales

--> May15(sales)/ 3 months(Feb,mar,apr )vg sales

am using below Calculation to achieve this. but am getting each individual date sales instead last 3 month sales..

Sum({< [Report Date]={">=\$(=MonthStart(Max([Report Date]), -3))<=\$(=MonthEnd(Max([Report Date]), -1))"}>} Saleable )

table shown below:

Creator III

I believe you need to use an aggregation.

Try:

AGGR( nodistinct Sum({1< [Report Date]={">=\$(=Date(monthstart(=max([Report Date]),-3), 'MM/DD/YYYY'))<=\$(=Date(monthend(=max([Report Date]),-1), 'MM/DD/YYYY'))"} Saleable ), [Report Date.autoCalendar.Month])

Creator III

And then to get the daily avg for the AGGR.

Take the AGGR and add the following:

/  (date(monthend(max( [Report Date]),-1))-Date(monthstart(max( [Report Date]),-3)))

MVP

I would look into using The As-Of Table here

Contributor III
Author

Hi Tm, Thanks for responding..

Not working  this approch... getting null values ,, any alternative way?

Creator III

Is the "Report Date" field  an AutoCalendar field.

Contributor III
Author

Yes, that is coming from auto calendar field and  also i have tried with normal date converting to year- month format.

and  1 more thing  i have tried below approach..

(Sum(Aggr(rangesum(above(sum(Saleable),1)), [Report Date.autoCalendar.YearMonth]))
+ Sum(Aggr(rangesum(above(sum(Saleable),2)), [Report Date.autoCalendar.YearMonth])) + Sum(Aggr(rangesum(above(sum(Saleable),3)), [Report Date.autoCalendar.YearMonth])))    Values  summingup correctly but its displaying only first  date of every month,, its not repeating till month end ( iam expecting to repeat  because  last 3 months its common  for all dates in perticular month)     screenshot below?

could you please suggest me how to drag these each month value to till month end ?

MVP

May be try this

Sum(Aggr(
RangeSum(Above(Sum(Saleable), 1, 3))
, [Report Date.autoCalendar.YearMonth]))

But isn't this doing it for just last 3 days? I thought you needed to do last 3 months? May be change 3 to 90?

Contributor III
Author

Not working Sunny..  even i placed 90 days..  its accumulating all 90 days.. eventhough displaying  only first date of every month..  and below  code working fine when I have placed  month as dimension.

but i  should hide that month  dimension in bar chart..     when i used condition show to hide this month dimension values also  going off.. could you suggest me any  way to achieve this?

= (Sum(Aggr(rangesum(above(sum(Saleable),1)), [Report Date.autoCalendar.Month] ))
+ Sum(Aggr(rangesum(above(sum(Saleable),2)),[Report Date.autoCalendar.Month] ))
+Sum(Aggr(rangesum(above(sum(Saleable),3)),[Report Date.autoCalendar.Month] )))