If function with Dates (YTD)

[EL_MAR]

I want to calculate the YTD of different fonds, throughout multiple years. 

I currently have a very static formula:

=Num((Sum({<Valuationdate={'29.12.2021'}>}[Adjusted NAV])-Sum({<Valuationdate={'29.01.2021'}>}[Adjusted NAV]))/Sum({<Valuationdate={'29.01.2021'}>}[Adjusted NAV]),'##,##%')

I did the formula for every year from 2009 to 2024. That formula is working, however, not very applicable, as it does not consider for the creation date of a fund. 

Here is selected a fund that was created on 14.05.2012, since it is not the ned of January the formula is not working for it.

I am looking for a solution for that problem. I thought maybe a if formula could work, where it says if the year is the year of the creation of the fund use the last working day of that month. If it is a regular day use the formula for last working day in January to last working day in december.

I added the fund creation date via the skript, Min(Date(Valuationdate)) as FundsOpeningDate

Does anyone have an idea?

Mein erster Versuch:

= Date(
MonthEnd(MakeDate(Year(Today()), 12)) -
If(WeekDay(MonthEnd(MakeDate(Year(Today()), 12))) = 0, 2,
If(WeekDay(MonthEnd(MakeDate(Year(Today()), 12))) = 6, 1, 0)),
'DD.MM.YYYY'
)

[Kushal_Chawda]

@EL_MAR  Not quite clear what exactly you are trying to do. Could you give few more example?

[marcus_sommer]

I would try to create all essential logic within the data-model - mostly within the master-calendar but maybe also in an extra dimension-table which contains the min/max dates per fund.

In the master-calendar you could add daynumberofyear() directly + as YTD flag and also a 0/1 flag of the working-days which are also accumulated with peek/previous in total/year/moth/week and further flags for the first/last calendar/working-day in a period and various similar stuff. All these information could then be referenced within the set analysis.

[EL_MAR]

Basically I am trying to have a table ( as seen above ) in which the YTD is calculated for each year since inception. My Problem is that each fund has an inception date that is somwhere within the year, for instance  un the example above I selected a fund that was created on 14.05.2012, since it is not at the end of January the formula is not working. And the year 2012 is left out.

The solution I think could be a if formula where the formula checks whether or not it is the inception date or not and regarding to that decisions calculates the static formula or the adapted one, for that specific year.

[EL_MAR]

I created a Flag in the script for the Ultimo date (The last working Date of each month) Through that I build a filter. With that filter activated I can calculate the YTD perfectly. Good advice

[EL_MAR · Accepted solution]

I used that formula:

=Num(
(Sum({<Valuationdate = {"$(=Date(Max({<Year={2022}>} Valuationdate)))"}>} [Adjusted NAV])
- Sum({<Valuationdate = {"$(=Date(Min({<Year={2022}>} Valuationdate)))"}>} [Adjusted NAV]))
/
Sum({<Valuationdate = {"$(=Date(Min({<Year={2022}>} Valuationdate)))"}>} [Adjusted NAV])
, '##,##%')

With the addition of a Flag in the script that filters for the Ultimo date of each month. That worked perfectly.