Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've seen many questions about this and tried the different solutions, but none have worked for me. I'm trying to get sales from the current quarter. Pretty simple. But I either get all sales or 0 sales. I've tried many variations. This is what I currently have:
Sum({$*BM13 <Month={'>= $(=Month(QuarterStart(today()))) <= $(=Month(QuarterEnd(today())))'}>}[Sales Amount])
Any ideas?
I think the problem is that the Month funcion returns string data. So, you cannot use the result in less or greater operators. Use num(month()), instead.
Eduardo
Would you be able to post a sample qvw?
The syntax you use is sensitive to spaces. Maybe this will work:
Sum({$*BM13 <Month={'>=$(=Month(QuarterStart(today())))<=$(=Month(QuarterEnd(today())))'}>}[Sales Amount])
I prefer a different approach:
Sum({$*BM13 <Month={{"$(= '>=' & Month(QuarterStart(today())) '<=' & Month(QuarterEnd(today())))"}>} [Sales Amount])
In any case, it is essential that Month and Month(QuarterStart(today())) are in exact same format.
Unfortunately Michael, neither option worked. I still get 0 as a result. If I use only one expression without the greater than less than, I get the expected result:
=Sum({$*BM13 <Month={"$(=Month(QuarterEnd(today())))"}>} [Sales Amount])
But obviously, this doesn't get what I need.
I think the problem is that the Month funcion returns string data. So, you cannot use the result in less or greater operators. Use num(month()), instead.
Eduardo
Your original expression only uses single quotes, should they be double quotes (honestly don't know if there is a difference in this kind of set analysis).
Eduardo, I could kiss ya! That worked. Thank you so much!
=Sum({$*BM13 <Month={'>=$(=Num(Month(QuarterStart(today()))))<=$(=Num(Month(QuarterEnd(today()))))'}>}[Sales Amount])
Your thank you is enough, hahaha
Eduardo