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

[kdr_Qv]

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.. 

[kdr_Qv]

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:

@sunny_talwar ,

@jagan 

[tm_burgers]

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])

[tm_burgers]

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)))

[sunny_talwar]

I would look into using The As-Of Table here

[kdr_Qv]

Hi Tm, Thanks for responding..

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

[tm_burgers]

Is the "Report Date" field  an AutoCalendar field. 

[kdr_Qv]

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 ?

[sunny_talwar]

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?

[kdr_Qv]

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] )))

@sunny_talwar @tm_burgers