topic Re: DAX for between a date range Q in Developer

DAX for between a date range Q

Anonymous — Wed, 22 Aug 2018 12:41:04 GMT

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

Re: DAX for between a date range Q

Greg_Deckler — Wed, 22 Aug 2018 12:44:48 GMT

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.

Re: DAX for between a date range Q

Anonymous — Wed, 22 Aug 2018 13:39:18 GMT

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.

Re: DAX for between a date range Q

Greg_Deckler — Wed, 22 Aug 2018 16:08:23 GMT

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.

Re: DAX for between a date range Q

Anonymous — Wed, 22 Aug 2018 18:50:18 GMT

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?

Re: DAX for between a date range Q

Greg_Deckler — Wed, 22 Aug 2018 19:55:13 GMT

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)] )

Re: DAX for between a date range Q

Anonymous — Thu, 23 Aug 2018 11:51:30 GMT

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

Re: DAX for between a date range Q

Anonymous — Thu, 23 Aug 2018 14:45:58 GMT

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