YTD at a given moment in time selected by user

[George_Metrakos]

Hello Community!

I'm trying to set a Year-to-date (YTD) expression such that the user can select the point in time (could be in the past) and the expression would calculate the YTD to that point.  I'm focussed on the MonthYear as my main time dimension.  Since this is a financial application I have budget (future) data as well as historic.

My current expression is only returning the sum of "AMOUNT" for the MonthYear selected, ie. if the user selects May2019, (link to Year = 2019 in data model) I would expect that this would sum AMOUNT from January to May of 2019 but it only gives me the AMOUNT for the selected May2019 period instead... Any ideas?

Thanks!

//YTD
=Sum({$<
Year={"$(=Max(Year))"},
MonthYear={"<=$(=Max(MonthYear))"}
>}AMOUNT)

[sunny_talwar]

May be you need MonthYear formatting added to your set analysis for MonthYear

=Sum({$<Year = {"$(=Max(Year))"}, MonthYear = {"<=$(=Date(Max(MonthYear), 'MMMYYYY'))"}>} AMOUNT)

[George_Metrakos]

Thank you @sunny_talwar , I appreciate your insight- unfortunately, even the date formatting isn't giving the correct result... for some reason, the "<=$" would sum up every MonthYear within the current Max(Year) but it's not... any other suggestions?

Thanks very much!

George

[sunny_talwar]

Would you be able to share a sample where we can see the issue?

[George_Metrakos]

Hi @sunny_talwar ,

I was able to figure it out- a syntax issue... the correct expression is:

=Sum({$<
Year={$(=Max(Year))},
MonthYear={"<=$(=Max(MonthYear))"}
>}AMOUNT)

The extra " on the Year set was throwing it...

Thanks again for taking a look!

George

[George_Metrakos]

Hello again @sunny_talwar ,

If you still have a chance to review, the problem I have now is to build the Prior YTD using the same user selection.

In my example, the user selected "May2019" as the MonthYear.  Therefore, my Prior YTD sum should be from Jan 2018 to May 2018 inclusive.  I'm using:

In my master calendar, I have a link to MonthNum, so May is "5" so I thought I'd use this:

Year={"=$(=Max(Year)-1)"},
MonthYear=,
MonthNum={"<=$(=Max(MonthNum))"},

I understood that adding the "MonthYear=" would negate the user selection so that the expression can utilize it to identify the Year and Month required...

However, the expression is not working- any thoughts?

Thanks,

George

[sunny_talwar]

Should be this

Year = {"$(=Max(Year)-1)"},

[sunny_talwar · Accepted solution]

or

Year = {$(=Max(Year)-1)},

[George_Metrakos]

Thanks @sunny_talwar !