Set Analysis

[jeckstein]

I need the Sum([Open Balance]) where the [SheetName] is the greatest (or latest date) the date is set up like MM/DD/YY.

I think the MM/DD/YY might be screwing up the set analysis. It maybe a text string and that could be an issue.

Thanks,

Jimmy

[Anonymous]

if your date is defined as text you need to convert it to a dateformat using the date# function

date#(yourtextdatefield,'MM/DD/YY')

[jeckstein]

Rudolf,

It is currently using the #Date.

 
      date(date#('$(vExcelSheets)','MM.DD.YY'),'MM/DD/YYYY') 

[sunny_talwar]

Look for fixing your date in the script. May be these links come in handy:

Get the Dates Right

Why don’t my dates work?

[Anonymous]

how is your Expression with set Analysis Looks like?

[jeckstein · Accepted solution]

I just figured it out. This was the expression I was looking for:

=Money(Sum({1<SheetName={"$(MaxSheetName)"}>}[Open Balance]))

$(MaxSheetName) was a variable I had to create that was defined as =Max(SheetName)