Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm having a syntax issue with a set analysis formula I am trying to create. The formula is trying to SUM invoices where dates fall between 2 dates and the posting date is greater than the end date. I've created 2 variables and I know these variables are correct because I've checked them.
=Sum({$<[Invoice Header.Order Creation Date] = {>= (vFromDate) <= (vToDate)}, [Date] = {> (vToDate)}>}[Invoice Line.Amount Including VAT])
vFromDate definition:
=(date(MonthStart(Max([Invoice Header.Order Creation Date])) - 365))
vToDate definition:
=(date(MonthEnd(Max([Date])) - 365))
When I use these variables alone they work but when I add them to the formula I get no results I think it's a syntax error but I don't see anything wrong with my formula.
Can someone help?
You seem to be lacking $-signs in front of the variables and furthermore you should also enclose the search string in double quotes:
=Sum({$<[Invoice Header.Order Creation Date] = {">=$(vFromDate)<=$(vToDate)"}, [Date] = {">$(vToDate)"}>}[Invoice Line.Amount Including VAT])
There is a slight inaccuracy in your calculation as not every year has 365 days. To be entirely accurate you would have to do something like this instead:
vToDate definition:
=Date( AddYear( MonthEnd( Max( [Date] ) ), -1) )
Actually since you will then do only date calculations you should be able to simplify it into this:
= AddYear( MonthEnd( Max( [Date] ) ), -1 )
And similarly with the other variable...