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

[tm_burgers]

I'm not sure why my AGGR solution does work for you - I have a very similar dataset that I used and tested it on and it worked well for me.

[kdr_Qv]

you mean  ur used below expression  right

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])                         /  (date(monthend(max( [Report Date]),-1))-Date(monthstart(max( [Report Date]),-3)))

Here also am getting  values only for the  first date  of every month ..  reamin dates null as shown below.

@tm_burgers 

[kdr_Qv]

HI SUnny ..

with This logic..  the values are coming only first day of the every month.. its not repeating till month end..

can you please let me know how to drag same  first date value till the month end..

@sunny_talwar @Gysbert_Wassenaar  @hic  @tresesco   @Antoniotiman 

[sunny_talwar]

Would you be able to share a sample where we can see the issue and try it out?

[kdr_Qv]

Hi Sunny , 

Please find my Qvf file attached here with sample data..

@sunny_talwar 

[sunny_talwar]

I see that you tried implementing As-Of Table. Would you be able to share SampleData.xlsx so that  I can try reloading the dashboard to try and fix AsOfTable to make this work.

[sunny_talwar]

Check the attached dashboard to see if this is what you want

[kdr_Qv]

 sunny, 

I need this  should split by dimension  as date field .. 

month wise  its working fine.. am expecting date wise..    for all june month dates  it should display same data( sum of mar, Apr, MAy month)

@sunny_talwar