Forum Discussion
DAX for between a date range Q
would like to run my SUMX() with a FILTER() between a date range that I want hard coded. What I have doesn't work when I add in the between date range component. See code below:
EnquiryTest = SUMX ( FILTER ( '_enquiries,' '_enquiries'[_tsg_clientid_value] = 'accounts[accountid']) &&
'_enquiries'[Date] => {08/01/2018} &&
'_enquiries'[Date] =< {08/01/2018}, _enquiries[Amount (Enq)] )
What is the proper syntax for a between date range?
Thank you
It should be:
EnquiryTest = SUMX ( FILTER ( '_enquiries,' '_enquiries'[_tsg_clientid_value] = 'accounts[accountid']) && '_enquiries'[Date] => DATE(2018/8/1) && '_enquiries'[Date] =< DATE(2018/8/31), _enquiries[Amount (Enq)] )
But, would need sample data to be sure.
Shoot, sorry about that, I meant to use commas:
EnquiryTest = SUMX ( FILTER ( '_enquiries,' '_enquiries'[_tsg_clientid_value] = 'accounts[accountid']) && '_enquiries'[Date] => DATE(2018,8,1) && '_enquiries'[Date] =< DATE(2018,8,31), _enquiries[Amount (Enq)] )
7 Replies
First, sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That being said, I do not understand why you need a between date range, your formula looks like you just want a particular date, August 1st, 2018.
It should be:
EnquiryTest = SUMX ( FILTER ( '_enquiries,' '_enquiries'[_tsg_clientid_value] = 'accounts[accountid']) && '_enquiries'[Date] => DATE(2018/8/1) && '_enquiries'[Date] =< DATE(2018/8/31), _enquiries[Amount (Enq)] )
But, would need sample data to be sure.