Set analysis with variable

[DaniCalifornia]

Help. I am just trying to get a variable into my set analysis. I know my variable is working because I have it writing to text fields and the table I'm trying to get this set analysis into. I have looked on the forums and my syntax seems to be right, but I cannot for the life of me to get any iteration of this to give me any financials whatsoever. I have transaction dates from 1988- July 2021. The end date is 5/31/2021 right now. It should sum everything prior to 5/31/2021, but I get nothing.

Here is my pared down set analysis. It's just this piece that does not work. 

=SUM({$<TransactionDate = {"<=$(vEndDate)"} >} FinancialMovement)

I suspect it has something to do with formatting?? My TransactionDate I tag as date in the load script. My variable is populated from a drop down of date values. They are in the same yyy-MM-dd format, but my transaction date has time stamps after it. But that should not matter in a <= comparison. 

Can someone please tell me where I am going wrong? I'm out of ideas. 

*Yes, I am using alternate states. I tried making it inherited though and no luck, and my variables pull into the table in the alternate state, so I don't believe that's the issue. 

Snapshot to show - the drop downs are date selections, the text boxes are my variable outputs. I put them in the table as well. The set analysis is updating the financial movement column and I get nothing. 

[rubenmarin · Accepted solution]

Hi, I usually prefer to have dates that I will use in set analysis in number, so I can forget about dates format.

If you load the date as Num(Floor(TransactionDate)) as TransactionDateNum, you will end with a date in number format with the time part removed and this expression should work: 

=SUM({$<TransactionDateNum = {"<=$(vEndDate)"} >} FinancialMovement)

[DaniCalifornia]

Thank you! I'm actually loading straight from SQL , not creating QVDs and all of that yet - because I'm still trying to get my model right. I added this to that table and tagged it as an integer:

,FLOOR(CAST([fct].[TransactionDate] AS [INT])) AS [TransactionDateNumber]

Then I wrapped my variable in a NUM() as well. Anywho, this works: 

=SUM({$<TransactionDateNumber = {"<=$(NUM(vEndDate))"} >} FinancialMovement)

Geez, data types!! Thank you for the suggestion. I've got everything there, just not matching on the stinking data types. 🙂 

[DaniCalifornia]

Oops, I lied a little.  That worked to get single transactions to show, but I had to add another = between ( and NUM to get it to sum right between dates. 

=SUM({$<TransactionDateNumber = {"<=$(=NUM(vEndDate))"} >} FinancialMovement)