calculation of date range

[oliviatww]

Hi there,

Not sure how to solve this problem. Please read my question below.

I have YEAR and MONTH list boxes where user get to select what year and month they want to look at.

Also, just to let you guys know that the year period is always start from 01/04 to next year 01/04 for example, [01/04/2012 - 01/04/2013] or [01/04/2013 - 01/04-2014] or [01/04/2014 - 01/04/2015] so on and so forth.

So when user select year and month, for example

[1] 201208, then I want to calculate the sum of the transaction amount from 201204 to 201208.

[2] 201309, then I want to calculate the sum of the transaction amount from 201304 to 201309.

[3] 201302, then I want to calculate the sum of the transaction amount from 201204 to 201302.

I'm not sure if I need to first compare the month, like if selected month is less than 04 then we use the previous year of selected year else we use the selected year. Not sure what's next...

Any help is highly appreciated. THANKS

[JonnyPoole · Accepted solution]

Here is a short sample. The data model includes fiscalyear and fiscalmonth calculated fields. 

The main selected is a field called CalendarYearMonth which allows users to select both a year and month.

The SET ANALYSIS to bring back the selected month and all previous months to the beginning of the fiscal year is as follows where

vSelectedFiscalYear = max(FiscalYear)

FiscalMonthNumber=max(FiscalMonth)

Note that you have to remove the users selection of calendaryearmonth and use the fiscalyear and fiscalmonthnumber modifiers to get the right months. Hope it helps.

sum({$<CalendarYearMonth=,FiscalYear={$(vSelectedFiscalYear)},FiscalMonthNumber={'<=$(vSelectedFiscalMonthNumber)'}>}  Sales)

[oliviatww]

Thanks a lot Jonathan for your help.