Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All
I have a requirement where I need to calculate average of last 6 months sales.
Example: If we are in December we need to calculate average from June to November..
In Jan, from July to Dec and so on. Kindly help to achieve this possibility
Thanks & Regards
Chintan Gala
Sorry try
and vEndYearMonth = Date(Addmonths( Date($(vStartYearMonth)),-6),'YYYYMM')
I had the same issue only a few weeks back with an application i was working on
In your script
LOAD Date(Date#([TheYear Month]), 'YYYY-MM'), 'YYYY-MM') as [The Year Month]
Your expression
Avg({$<[The Year Month] = {">=$(=Date(AddMonths(Max(Today()),-6), 'YYYY-MM'))<=$(=Date(AddMonths(Max(Today()),-1), 'YYYY-MM'))"}>} [Your Value to Measure])
The expression will always calcalate the date between the last 6 months and last month always so for this month, you will get Dec last month and 6 months previous. Next Month, you will get Jan and 6 months previous. Make sure to declare [The Year Month] in your load script.
Hi David,
What worked for me was a little different but very similar. The main thing that I changed was to replace the [The Year Month] dimension to the actual [Date] dimension.
Added to load script:
LOAD Date([Date]), 'YYYY-MM'), as [The Year Month]
Calculation expression in app:
Avg({$<[The Year Month]={">$(=Date(AddMonths(Today(),-6),'YYYY-MM'))<=$(=Date(AddMonths(Today(),-1),'YYYY-MM'))"}>}[Your Value to Measure])
It is working and calculating previous 6 months.
Thanks,
LD
Hi Luiz,
Delighted it worked. Took me ages to get that working also.
Please mark as correct.
Hello Luiz
As you are using today()-6 , I guess it will go to previous 6 months from today. This would not work if we select any previous month as filter.
Thanks & Regards
Chintan Gala
Hello Chintan,
You would have to add an IF condition then.
Ex:
If( (GetSelectedCount (MonthYear) = 0 and GetSelectedCount (Year) = 0),
Avg({$<[The Year Month] = {">=$(=Date(AddMonths(Today(),-6), 'YYYY-MM'))<$(=Date(AddMonths(Today(),0), 'YYYY-MM'))"}>} [Your Value to Measure]),
Avg([Your Value to Measure]))
This expression will give you either last 6 finished full months if no date as to MonthYear or Year is selected, or the AVG of whatever months you select.
Or
If( (GetSelectedCount (MonthYear) = 0 and GetSelectedCount (Year) = 0),
Avg({ $ <[The Year Month] = {">=$(=Date(AddMonths(Today(),-6), 'YYYY-MM'))<$(=Date(AddMonths(Today(),0), 'YYYY-MM'))"}>} [Your Value to Measure]),
Avg({ 1 <[The Year Month] = {">=$(=Date(AddMonths(Max(Date),-6), 'YYYY-MM'))<$(=Date(AddMonths(Max(Date),0), 'YYYY-MM'))"}>} [Your Value to Measure]))
This expression will give you either last 6 finished full months if no date as to MonthYear or Year is selected, or the AVG of last 6 months from the MonthYear you select. Make sure you use 1 instead of $ on the second part of the expression.
Cheers,
LD
Dear Vineeth
I am unable to create the previous 6 months from
vEndYearMonth = Date(Addmonths( Date($(vStartYearMonth)).,-6),'YYYYMM')
Is there any other way around?
Regards
Chintan
Date(Addmonths( Date($(vStartYearMonth),'YYYYMM'),-6),'YYYYMM')
There was a full stop before comma .,
Try this
Avg({$<Date={"<$(=MonthEnd(AddMonths(Max(Date),-1))>$(=MonthStart(AddMonths(MonthEnd(AddMonths(Max(Date),-1)), -6)))"}>}Value)
It shows expression OK, but later shows error as ')' expected.