Selected Dates Range

[bharatkishore]

Hi All,

I have a date field from Jan 2014 to Sep 2017.

I need to calculate sum(consumption) from Oct 2015 to Sep 2016

then I need to calculate sum(consumption) from Oct 2016 to Sep 2017

Can you please tell me how can i achieve this.

Attached app for more reference.

Thanks,

Bharat

[tresesco]

In text boxes? Are these ranges static or you have some logic to decide upon the ranges?

[bharatkishore]

Hi Tresco,

It will be based on the dates selection,

For example I have dates starting from Jan 2014 to Sep 2017.

If i select Sep 2017 the first expression i.e. sum(consumption) should calculate last 11 months i.e. Oct 2016 to Sep 2017.

And in the second expression i.e. sum(consumption) should calculate last 11 months i.e. Oct 2015 to Sep 2016.

The same way if i select Aug 2017 date range should be Sep 2016 to Aug 2017

And second expression should be Sep 2015 to Aug 2016

Please let me know if you require anything more.

[poojashribanger]

Hi,

You are saying your fiscal year starts from October and ends in September

[poojashribanger]

Hi,

You are saying your fiscal year starts from October and ends in September

[bharatkishore]

Hi Poojashri,

It is not the fiscal year.. the calculation should be done based on last 11 months from what ever date i select..

[tresesco · Accepted solution]

Try like:

Last year:

=Sum({<Year, Month,[MonthYear]={">=$(=MonthStart(Max(MonthYear),-11)) <=$(=MonthStart(Max(MonthYear)))"}>}Consumption)

Last to last year:

Sum({<Year, Month,[MonthYear]={">=$(=MonthStart(Max(MonthYear),-23)) <=$(=MonthStart(Max(MonthYear),-11))"}>}Consumption)

[bharatkishore]

Thanks a lot.. Thank you so much...

If you don't mind can you explain me the logic..

[tresesco]

I am lazy and bad in explaining too (especially when it is non-verbal). However, if you could ask a specific portion of the expression you could not follow, I might give a try.

[bharatkishore]

Can you please tell me why we have taken these Year, Month and Month Year and why month start and max(monthyear) and how to know what number to give like how you have given 11

({<Year, Month,[MonthYear]={">=$(=MonthStart(Max(MonthYear),-11))