Date set analysis problem

[#1]

Hi Everyone,

I am trying to make it so that when I select a certain month that the current year's data up until that month is also included in my calculation. For example:

I select February of this year, it is currently June. I just want to see the data from January and February of this year, not January - June. What would be the best way to do this without having to select both January and February.

Any help on this would be greatly appreciated.

Thanks!

Joe

[MK_QSL]

Do you have date field?

If yes, you can achieve this using date field...

=SUM({<InvoiceYear=, InvoiceMonth=, InvoiceDate = {">=$(=YearStart(Max(InvoiceDate)))<=$(=Max(InvoiceDate))"}>}Sales)

Change InvoiceYear, InvoiceMonth and InvoiceDate field names respective to your data model.

[effinty2112 · Accepted solution]

Hi Joe,

          Try this set modifier in your expressions.

{$<Year = {$(=max(Year))}, Date = {'<=$(=max(Date))'}, Month =>}

I'm assuming you have a calendar with a Year field. If you have any other selectable calendar fields like Qtr you should add , 'Qtr =' to the above set so that if you select the 2nd Qtr then select a date in that Qtr your expression will total from the beginning of the year not just from the beginning of the 2nd Qtr..

Cheers

Andrew

[#4]

Thank you! Much appreciated.

[effinty2112]

You're very welcome Joe!