SUM Criteria

[#1]

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

example data attached.

my question is about this formula:

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

[sunny_talwar]

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

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

[#3]

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

[sunny_talwar]

What exactly do you want to see?

[#5]

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

[sunny_talwar · Accepted solution]

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?

[#7]

ok... it works!

let's hope this work for next year. 😃

[sunny_talwar]

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