7 Replies Latest reply: Jul 22, 2015 7:59 PM by Sunny Talwar

# SUM Criteria

Hi All, I have made a similar question before, but I don't know if this apply as the one before.

example data attached.

SUM({<[Fiscal Year]={'\$(=Year(TODAY()))'}>}[Netsales])

What I understand this is: Sum all Netsales that criteria for [Fiscal Year] is today's year is 2015. out Fiscal Year end on sept 30th, but if today were Oct 1st, 2015, that formula will still show year 2015.

so now, within the date table, I have Date field that can match the Fiscal Year field.

How can I do this?

Thanks

• ###### Re: SUM Criteria

You would want to see Sales for FY2016 after Oct 1st, 2015??? May be this:

Sum({<[Fiscal Year] = {"\$(=Max([Fiscal Year]))"}>} Netsales)

• ###### Re: SUM Criteria

no work... tried that... my tables have fiscal year 2016.

• ###### Re: SUM Criteria

What exactly do you want to see?

• ###### Re: SUM Criteria

I want to see Fiscal Year 2015, without any need to have the filter selected. If I use Max Fiscal Year, I have 2016 in there. so this is giving me 2016 data.

the Date table have everything that can be matched, but don't know how to tell using expression that

sum all netsales that fiscal year is today's date.

So, once I have this information, if I reach Oct 1st, 2015 which is Fiscal Year 2016, it will go there automatically.

not sure if you get it?

thanks

• ###### Re: SUM Criteria

Try this may be:

Sum({<[Fiscal Year] = {"\$(=If(Num(Month(Today())) = 10, Year(Today()) + 1, Year(Today())))"}>} Netsales)

This should give Fiscal Year 2015 until 30th Sept 2015 and on Oct 1st it should give you Fiscal Year 2016.

Is that what you wanted?

• ###### Re: SUM Criteria

ok... it works!

let's hope this work for next year. =)

• ###### Re: SUM Criteria

Just for trying, see if this work and gives you 2016

Sum({<[Fiscal Year] = {"\$(=If(Num(Month(AddMonths(Today(), 4))) = 10, Year(Today()) + 1,Year(Today())))"}>} Netsales)

If this gives you result for 2016, I don't see why the above expression won't work.

Best,

Sunny