Calculate the same last year period

[caccio88]

Hi everyone,

what i want is to compare year to date period with the same last year period. The max date of my data is July 2016 and the year starts in March 2016. I've already created my new fiscal year from March to February but I can't do the right calculation of the last year in the same period.

At the end I want the value from March 2015 to July 2015.

I tried reading this Year-over-Year Comparisons, but wasn't so helpful for me.

Here attached my example.

stalwar1 swuehl gwassenaar cleveranjos

[Clever_Anjos]

I think the best solution would be using a As-Of table with proper flag to what you need

The As-Of Table

[sunny_talwar]

May be try this:

Sum({<DataMonthYear ={"$(='>=' & Date(YearStart(Max(DataMonthYear),-1, 3), 'MMM-YYYY') & '<=' &

$(='Date(AddYears(Max({<DataMonthYear = {' & Chr(34) & '=Sum({<CATEGORY={' & Chr(39) & 'ACTUAL' & Chr(39) & '}, ACCOUNT={' & Chr(39) & 'CFFCF' & Chr(39) & '}>}Measures2)' & Chr(34) & '}>}DataMonthYear), -1), ' & Chr(39) & 'MMM-YYYY' & Chr(39) & ')'))"}, CATEGORY={'ACTUAL'},ACCOUNT={'CFFCF'}>} Measures2)/1000000

[caccio88]

The results is perfect! Thank you Sunny! stalwar1‌ But I can't clearly understand how it works...

[sunny_talwar]

I had to go through a painful process of making it to work . I am outside for a little bit, but can brief you on how its working once I am near my computer .

[sunny_talwar]

Basically this

='>=' & Date(YearStart(Max(DataMonthYear),-1, 3), 'MMM-YYYY') & '<=' &

$(='Date(AddYears(Max({<DataMonthYear = {' & Chr(34) & '=Sum({<CATEGORY={' & Chr(39) & 'ACTUAL' & Chr(39) & '}, ACCOUNT={' & Chr(39) & 'CFFCF' & Chr(39) & '}>}Measures2)' & Chr(34) & '}>}DataMonthYear), -1), ' & Chr(39) & 'MMM-YYYY' & Chr(39) & ')')

in a text box object gives you the range you needed

The Mar-2015 was easy to get

Date(YearStart(Max(DataMonthYear),-1, 3), 'MMM-YYYY')

But July-2016 was difficult because the max date was Feb-2017, but for this particular expression Sum({<CATEGORY={'ACTUAL'},ACCOUNT={'CFFCF'}>} Measures2) the max date was July-2016. To get this, I had to use a set analysis, but doing a double quote within a double quote is an issue, so I had to use a dollar sign expansion to get this to work

$(='Date(AddYears(Max({<DataMonthYear = {' & Chr(34) & '=Sum({<CATEGORY={' & Chr(39) & 'ACTUAL' & Chr(39) & '}, ACCOUNT={' & Chr(39) & 'CFFCF' & Chr(39) & '}>}Measures2)' & Chr(34) & '}>}DataMonthYear), -1), ' & Chr(39) & 'MMM-YYYY' & Chr(39) & ')')

The above gives me July-2015 because I use AddYears(July-2016, -1)

Does this help explain what the expression is doing?

[caccio88]

Wonderful explanation! the best as usual! thank you Sunny! stalwar1‌

[caccio88]

stalwar1‌ tha solution works without other selection but if I select on DataMonthYear like Jul-16 the set doesn't works.... so with the selection mentioned before we don't have -1,68 ( the value of Jul-15) but always -61,70.

Any idea for this problem?

[sunny_talwar]

Isn't the idea of YTD to show always show a YTD number even if you select a single MonthYear? When you select Jul-2016, showing Jul-2016's number would defeat the purpose, no? I see that your current year's expression is doing that and we might be able to configure your other expression to do that as well, but currently your last year's expression is behaving like a true YTD expression which does change when you select Jun-2016 by summing up the number from Mar-2016 till Jun-2016. Is this not what you would want and see just one month's number on selection of a month?

[sunny_talwar · Accepted solution]

Here is the expression which will meet your requirement of selection of a particular month

Sum({<DataMonthYear = {"$(='>=' & If(GetSelectedCount(DataMonthYear) >= 1, Date(AddYears(Min(DataMonthYear), -1), 'MMM-YYYY'), Date(YearStart(Max(DataMonthYear),-1, 3), 'MMM-YYYY')) & '<=' &

$(='Date(AddYears(Max({<DataMonthYear = {' & Chr(34) & '=Sum({<CATEGORY={' & Chr(39) & 'ACTUAL' & Chr(39) & '}, ACCOUNT={' & Chr(39) & 'CFFCF' & Chr(39) & '}>}Measures2)' & Chr(34) & '}>}DataMonthYear), -1), ' & Chr(39) & 'MMM-YYYY' & Chr(39) & ')'))"},

CATEGORY={'ACTUAL'},ACCOUNT={'CFFCF'}>} Measures2)/1000000