Using Billing Periods instead of Months

[#1]

I am striking out on this set analysis....

I have the following columns in my data set:

CHG AMT:  Amount that was charged for the item

CHG Date:  The date the item was charged

CHG Period: It is a Year/Month combination using our BILLING Calendar.  So the 1st of every month belongs to the month before.

                              Ex.  CHG DATE= March 1, 2012

                                     CHG Period =  201202

                                             BUT

                                    CHG DATE= March 2, 2012

                                     CHG Period =  201203

I am trying to get a constant that represents the total amount billed (CHG AMT), for the most recent [CHG Period] based off the recent date in the file (Max(CHG DATE))

Sample Data:

CHG AMT               CHG Date                    CHG Period

15.50                       2/29/2012                        201202

20.75                       3/01/2012                        201202

10.25                       3/01/2012                        201202

  5.50                       3/02/2012                        201203

  8.00                       3/03/2012                        201203

10.65                       3/03/2012                        201203

Max(CHG DATE)=3/3/2012

Which is Period--> 201203

Sum of Charges for all of 201203: Expected Answer $24.15

Any Advice?

[Gysbert_Wassenaar]

This should return the sum of CHG AMT for the maximum CHG Date:

sum({<[CHG Date]={'$(=max([CHG Date]))'}>} [CHG AMT])

[#3]

I seem to be getting 0 for an answer...

I added a sample to my original post that contains the simple data set from my sample data.

[Gysbert_Wassenaar]

Your dates have a different format than the document default. Try formatting the date:

=sum({<[CHG Date]={"$(=date(max([CHG Date]),'M/DD/YYYY'))"}>}[CHG AMT])

[#5]

That fixes the formatting issue but the total CHG AMT comes out to $18.65.  It is only summing the Max(CHG Date).  What I am looking for is the total CHG Period that the Max(CHG Date) is in.

So if 3/3/2012 is the max date.... that is in CHG Period 201203

so the answer would be the sum of all dates in the 201203 CHG Period, which equals $24.15

Thank you for your help, I really appreciate it.

[Gysbert_Wassenaar · Accepted solution]

=sum({<[CHG Period]={"$(=max([CHG Period]))"}>}[CHG AMT])

[#7]

Thank you so much!  That worked like a charm.  I think I was over complicating it for no reason.

Thanks Again!

[#8]

Hi Gysbert,

Can I ask you one more question....

Do you know why the 1 is not working to ignore the selection?

=NUM(sum({1<[CHG Period]={"$(=(max([CHG Period])))"}>}CHG_AMT),'$###,000.00' )

Thanks

[Gysbert_Wassenaar]

max([CHG Period]) still takes selections into account. If you want this to always be the absolute maximum then change it to max({1}[CHG Period])

[#10]

I just learned something new! I did not know you could put the {1} there.  All the examples I was looking at showed it in the beginning before the <>.

Thank you for all your help, and your SUPER fast reply!