Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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)] )
EnqAmt =
SUMX (
FILTER ( _enquiries, _enquiries[_tsg_clientid_value] = accounts[accountid] && _enquiries[Date] >= DATE(2018,08,01) && _enquiries[Date] <= DATE(2018,08,31) ), _enquiries[Amount (Enq)]
)
#this_works
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.
I am wanting a between dates filter. I can't use the current month, because that won't work when the month changes. I would like to see the hard coding of a between date range. What would it look like with the beginning of August to the end of August.
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.
Sadly, I am getting the following error message:
Too few arguments were passed to the DATE function. The minimum argument count for the function is 3.
I have changed the / to , but that doesn't work. If I change the month from 8 to 08 - it returns the same error. Any thoughts?
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)] )
Thank you for all of the help, Greg. I am still getting errors, but I have ordered some books to read over the weekend.
Respectfully,
J Morningstar
EnqAmt =
SUMX (
FILTER ( _enquiries, _enquiries[_tsg_clientid_value] = accounts[accountid] && _enquiries[Date] >= DATE(2018,08,01) && _enquiries[Date] <= DATE(2018,08,31) ), _enquiries[Amount (Enq)]
)
#this_works
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
2 | |
2 | |
1 | |
1 |
User | Count |
---|---|
21 | |
2 | |
2 | |
2 | |
2 |