Calculating Current YTD amounts for Current Year and Previous Year Excluding the current month

[AH]

Hi All,

I would really appreciate of any kind of help in this problem.

I have a scenario where i have to calculate the sum of Line amount for the Current year and previous year excluding current month.

But for the Previous year calculation it will show only the months that are completed in 2015.

So in summary:

Line Amount in 2015(Actual 2015): Sum of all months excluding Current month

Line Amount in 2014(Actual 2014): Sum of All months(All months of 2015) excluding the current month (The month is current for 2015)

Please let me know if you have any questions.

Thanks in advance.

Ahmed

[maxgro]

sum({$ <[Invoice Date]={">=$(=YearStart(Today()))<=$(=Date(MonthStart(Today())-1))"}>} LineAmount)

sum({$ <[Invoice Date]={">=$(=AddYears(YearStart(Today()),-1))<=$(=Date( AddMonths(   MonthStart(Today())-1,-12)   ) )"}>} LineAmount)

[sunny_talwar]

This may be:

This:

Actual 2015 (Excluding Month 5)

=Sum({<[Invoice Year] = {"$(=Max([Invoice Year]))"}, [Invoice Month] = {"$(='<' & Max({<[Invoice Year] = {'$(=Max([Invoice Year]))'}>} [Invoice Month]))"}>}LineAmount)

Actual 2014 (Excluding Month 5)

=Sum({<[Invoice Year] = {"$(=Max([Invoice Year])-1)"}, [Invoice Month] = {"$(='<' & Max({<[Invoice Year] = {'$(=Max([Invoice Year]))'}>} [Invoice Month]))"}>}LineAmount)

[AH]

Thanks Sunindia!

Big help!!

Ahmed

[AH]

Hi Sunindia,

I am having a little problem with the filtering by time in a chart i added in the document.

When i click year 2014 the line chart for year 2014 is disappearing and year 2015 line is showing for 2014.

Another thing is, If the user want to view the amount of month 5 (even though its excluded from the calculation) by filtering/clicking month 5 can they see the amount in the chart? Is it possible to do that in the calculation that you provided in the expression?

Thanks,

Ahmed

[sunny_talwar]

you don't want your chart to be dynamic based on selection? you always want to see 2015 and 2014 comparison???

[sunny_talwar]

Try this

[AH]

Hi sunindia,

I really appreciate your replies. Yes, i would like to have the chart to be dynamic based on selections. But since my chart should be a comparison between Current year and previous year so the Year is 2015 and 2014 in the chart.

The problem I am having with the solution provided is, when i selected 2014 the table values for 2014 shows in the column of 2015.

Also, If i select June/July of 2014 it does not show any value.

By default the chart would be showing 2014 & 2015 values excluded the current month. But i would like to have the option to bring the values of current months and also beyond the current months of 2014 whenever i select. I am not sure if it possible but i definitely appreciate and would be really happy if we can come up with the solution.

Thanks i advance.

Ahmed

[AH]

Hi Massimo,

Your solution is serving almost 80% of my purpose. Only two thing i would like to have is,

1. When I select the fifth month (May) then i would be able to see that in the chart Although 5th month(May) is excluded from the calculation and the chart is showing the values of 4th month (April) by default. Is it possible to achieve.

2. Sale for the table as well.

Thanks,

Ahmed