Hi, how can i add formula to calculate in year savings that changes as per fiscal year. My fiscal year starts in Jun and ends in july. I am able to calculate for current fiscal year. I want it to work for all fiscal years. Ex: a project with due date in Jun’22 will only have saving for 1 month. A project completed in Aug’22 will have savings for 11 months till jul’23 and so on.
Currently using this:
Benefit days fiscal’22 = DATEDIFFDAYS([Time.Fiscal].CurrentHierarchyMember.name,‘30-06-2022’)
In year savings =
case when [Measures].[Benefit days Fiscal’22]<366 and [Measures].[Benefit days Fiscal’22]>0
then
(
[Measures].[Actual Measured Contact Reduction Value $ with expected benefit date]
/365
)*[Measures].[Benefit days Fiscal’22]
ELSE
[Measures].[Actual Measured Contact Reduction Value $ with expected benefit date]
end
I see you are using Fiscal hierarchy in the Time dimension. It has the same structure as the default hierarchy - Year-Quarter-Month-Day.
The idea is to find out the current level of the hierarchy within the Time dimension, climb back to the level of Year, and then retrieve the start date of the current fiscal year.
Once the start date is known, we can add one year.
Since we need one day before that - we might subtract the extra day at the end of the measure.
So the expression for the ‘Benefit days fiscal’ might be as follows.
DateDiffDays(
[Time.Fiscal].CurrentHierarchyMember.StartDate,
--beginning of next fiscal year
DateAdd(
"yyyy",
1,
CASE [Time.Fiscal].CurrentHierarchyMember.Level.Name
WHEN 'Year'
THEN [Time.Fiscal].CurrentMember.StartDate
WHEN 'Quarter'
THEN [Time.Fiscal].CurrentMember.Parent.StartDate
WHEN 'Month'
THEN [Time.Fiscal].CurrentMember.Parent.Parent.StartDate
WHEN 'Day'
THEN [Time.Fiscal].CurrentMember.Parent.Parent.Parent.StartDate
END
)
)
--subtract one day to stay at the end of the current fiscal year
-1
Since the new version of ‘Benefit days fiscal’ stays within limits 0-366, you might need to adjust the other measure to check if the start date for savings is within the current fiscal year.
Function DateInPeriod might help with that - DateInPeriod.