set analysis with sum max

[TomBond77]

Hi experts

I have the below formula but it is not working. This KPI should only show values if the selected YearMonth is greater equal to 01.07.2023. All filtered months like  '2023-Jun', '2023-May', etc should be ignored, without any value. Any ideas how to get the formula working? Thank you.

sum(

{<[YearMonth] >= {"$(=MonthEnd(Max(YearMonth))>='01.07.2023')"}>}

Amount)

[LRuCelver]

This requires you YearMonth field to be a date field:

{<YearMonth = {">=$(=Date(MakeDate(2023,7,1), 'YYYY-MMM'))"}>}

I used MakeDate to get a date. You can use other fields or functions. 

[TomBond77]

Thanks, this gives me always zero as result...any ideas?

[TomBond77]

I need something like this:

if(max([YearMonth])>=Date(MakeDate(2023,7,1), 'YYYY-MMM'),
sum({<[YearMonth]={"$(='>=' & '01.07.2023' & '<=' & MonthEnd(Max(YearMonth)))"}>}
Amount))

But it gives me only 2023-Dec value, I have selected '2023-Dec' as YearMonth filter value. I would like to see values from '2023-Jul' onwards if the selected YearMonth filter value is >= '01.07.2023'. If not, I don't want to see any values. Any ideas?

[LRuCelver]

Please carify these points:

1. What is the level of detail of the YearMonth field (days/months) and how is it formatted?
2. Is "Rolling 12 Months" using the YearMonth field or is it a separate field?
   1. In case it is a separate field: Is YearMonth only used to select an upper bound for the date?

[TomBond77]

1.YearMonth comes from autocalendar and has 'YYYY-MMM' format. It is derived from timestamp. But        autocalendar format it to YYYY-MMM.

2. Rolling 12 months is based on YearMonth, coming from the autocalendar. The rolling 12 months based on this dimension YearMonth is included in the formula above.

Is this now clear?

[TomBond77]

tried this but still not working! A filtered YearMonth value of '2023-Jun' should not give any result. Only a filtered YearMonth value of '2023-Jul' and greater than '2023-Jul' should give a result:

sum(
{<[Timestamp.autoCalendar.YearMonth]={"(='>=' & '01.07.2023')"},
[Timestamp.autoCalendar.YearMonth]={"$(='>=' & Monthstart(Addmonths(Max(Timestamp.autoCalendar.YearMonth),-11)) & '<=' & MonthEnd(Max(Timestamp.autoCalendar.YearMonth)))"}
>} Amount)

[LRuCelver · Accepted solution]

I've added a variable vCutoffYearMonth = Date(MakeDate(2023,7), 'YYYY-MMM').
Try this:

{<YearMonth = {"$(=If(Max(YearMonth) >= vCutoffYearMonth, '>=$(vCutoffYearMonth) <=$(=Max(YearMonth))'))"}>} Sum(Amount)

[TomBond77]

Thanks, 

This now my formula:

I have filtered on September 2023, I expect now to see the values from Jul-2023 until Sep-2023.

I only see the filtered month Sep-2023:

[TomBond77]

got the solution: vCutoffYearMonth= '01.07.2023'.