Need Help with Calculation

[HigherEdCognosGuru]

I am wanting to create a calculation as follows:

~ Transactions from July 1 from 2017 through the current date (Example: 07/01/17 - 01/08/18)
~ Take the activity from the items above and get the previous year through the current date minus a year. (Example: 07/01/16 - 01/08/17)
~ Take the activity from the current year and go back two years. (Example: 07/01/15 - 01/08/16)

I attempted the following:

Query 1:
Capture Current Year
Fiscal Year = case when extract(month, current_date) > 6 then extract(year, current_date) +1 else extract(year, current_date) end

Query 2
Capture Previous Year
If(extract(year, [JMU PV Funds Raised].[Funds Raised Gifts, Matches and Pledges].[Date of Record for Gift, Pledge or Match]   ) = extract(year, current_date) - 1 and [JMU PV Funds Raised].[Funds Raised Gifts, Matches and Pledges].[Date of Record for Gift, Pledge or Match]  <= _add_months (  current_date , -12))then ([JMU PV Funds Raised].[Funds Raised Gifts, Matches and Pledges].[Hard Credit $ Amount])else (0)

The issue with this query is with the switch to the new year its only capturing January for last year. I have racked my brain on this one.

Query 3
Capture Two Years Back

If(extract(year, [JMU PV Funds Raised].[Funds Raised Gifts, Matches and Pledges].[Date of Record for Gift, Pledge or Match]   ) = extract(year, current_date) - 2 and [JMU PV Funds Raised].[Funds Raised Gifts, Matches and Pledges].[Date of Record for Gift, Pledge or Match]  <= _add_months (  current_date , -24))then ([JMU PV Funds Raised].[Funds Raised Gifts, Matches and Pledges].[Hard Credit $ Amount])else (0)

Same issue as query 2

Any help is greatly appreciated.

[Invisi]

Does your date dimension have a year-month column that looks like 201706 for instance? Then you can simplify your solution I think.

[hespora]

Code Select Expand

case when
_days_between (
  [Date of Record for Gift, Pledge or Match] ,
  _make_timestamp ( _year ([Date of Record for Gift, Pledge or Match]), 7, 1 )
)
< 0
then _year ([Date of Record for Gift, Pledge or Match]) - 1
else _year ([Date of Record for Gift, Pledge or Match])
end
that's your fiscal year on the [Date of Record for Gift, Pledge or Match]. compare that to current fiscal year you already have.

now you want ytd on that. just calculate _days_between for current_date vs. start of current fiscal year and compare that for the recorded timestamps.

/Edit: just realized, my definition doesn't fit yours. On July 1st 2017, what fiscal year started for you? If it's 2017, then use what I supplied above. If it's 2018, then remove the "- 1" in the the statement, and add a "+ 1" in the else statement instead.