not match function

[TomBond77]

Hi experts

I have this rolling 12 months period. 

Sum({<%Date= {">=$(=MonthStart(Max(%Date), -12))<=$(=Date(Max(%Date)))"}>} $(='Fact.AMOUNT_'&$(v_GCLC)&'_YTD_AC'))

But only Profit Center 1000 and 2000 should be calculated by this logic. Profit Center 3000 should only be calculated until July 2022.

How should the "match" logic should look like?

Thank you, Tom

[neerajthakur]

What do you mean by Profit Center?

[TomBond77]

this is a dimension.

[neerajthakur]

if([Profit Center]=1000 or [Profit Center]=2000,Sum({<%Date= {">=$(=MonthStart(Max(%Date), -12))<=$(=Date(Max(%Date)))"}>} $(='Fact.AMOUNT_'&$(v_GCLC)&'_YTD_AC')),if([Profit Center]=3000 and MonthEnd(%Date)<='Enter date in format you are using',

Sum({<%Date= {">=$(=MonthStart(Max(%Date), -12))<=$(=Date(Max(%Date)))"}>} $(='Fact.AMOUNT_'&$(v_GCLC)&'_YTD_AC'))))

[TomBond77]

Thanks, I need to work with "match" function as there are more than 3 Profit Centers. But this formula is not working. any ideas?

if(match([ProfitCenter], '3000', '3005', '3010'
Sum({<%Date={">=$(=MonthStart(Max(%Date), -12))<='31.07.2022'"}>} $(='Fact.AMOUNT_'&$(v_GCLC)&'_YTD_AC'))))

[neerajthakur]

Use Pick Match

Pick(Match([ProfitCenter], '3000', '3005', '3010'),Expression with Profit Center=3000,.......so on)

You will have write it for each separately.

For Example:

Sum({<%Date= {">=$(=MonthStart(Max(%Date), -12))<=$(=Date(Max(%Date)))"},[Profit Center]={'3000'}>} $(='Fact.AMOUNT_'&$(v_GCLC)&'_YTD_AC'))

[TomBond77]

I tried this formula: But it is ignoring the month 12 of 2021. Filter on year is 2022. Furthermore it is still adding / summing values after 01.06.2022. Any ideas?

if([Profit Center]= 'U3000' or [Profit Center]= 'U3005' or [Profit Center]='U3010',
Sum({< [Year]= , [Month]=,  %Date={">=$(=MonthStart(Max(%Date), -12))<='01.06.2022'"}>} $(='Fact.AMOUNT_'&$(v_GCLC)&'_YTD_AC')))