Multiple filters not working in DAX

[Anonymous]

Hi,

I have below DAX formulas and count measure is not returning the result I am expecting:

Total_Amount = calculate(sum(Giving[Giving_Amount),ALLEXCEPT('Giving','Giving'[AccountId]))

Count = calculate(DISTINCTCOUNT(Giving[AccountId]),filter(Giving,[Total_Amount]>=1000&&[Total_Amount]<=9999),filter('Date','Date'[CurMonthOffset]<=-1 && 'Date'[CurMonthOffset]>=-12))

There is a one to many relationship between giving (close date) and date table.

I am trying to get the number of distinct accounts who gave us between $1,000 and $9,999 in the last 12 months (not including the current month).

I created total amount measure because some people give us $500 twice and I want to catch these people too.

When I vizualize count measure, it returns more people I am expecting. BUT, as soon as I add a filter to the vizual like the picture, it returns the correct result.

Any idea why my filter in DAX not working?

[Anonymous]

Here is the sample data:

Date	Amount	Account
Saturday, January 30, 2021	500	A
Wednesday, December 30, 2020	500	A
Wednesday, December 30, 2020	1000	B
Saturday, October 10, 2020	15000	C
Tuesday, September 1, 2020	5000	D
Saturday, August 1, 2020	5000	D
Monday, January 1, 2018	500	A

I changed the total amount DAX formula:

Total Giving Amount =

var _max = EOMONTH(TODAY(),-1)

var _min = date(year(_max)-1, month(_max)+1,1)

return calculate(sum(Giving[Giving Amount]),filter('Date','Date'[Date]<=_max && 'Date'[Date]>=_min))

- This would return the data between 2/1/2020 - 1/31/2021.

I have another formula to count the account spent between 1000 and 9999.

Count Last 12 Months = CALCULATE(DISTINCTCOUNT(Giving[Account]),filter(Giving,Giving[Total Giving Amount]>=1000 && Giving[Total Giving Amount] <= 9999))\

This is what I get for total giving amount.

 

A should be included as this person spent more than $1,000 in this time period. Also, D should be excluded as this person gave more than $10000.

 Any idea what I am doing wrong?

[V-lianl-msft]

Hi @Anonymous ,

It is very difficult to analyze without looking at the data and just by imagining.

Could you share the sample pbix via cloud service like onedrive for business?

How-to-provide-sample-data-in-the-Power-BI-Forum 

Please remove any sensitive data before uploading.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, please see the reply to my original post. I somehow couldn't reply to your post.

[amitchandak]

@Anonymous , I doubt current month offset has some problem,

You can get rolling 12 like

example

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

or

max Close =
var _max = maxx(allselected([Date]), 'Date'[Date])
var _min = date(year(_max)-1, month(_max),1)
return
calculate(DISTINCTCOUNT(Giving[AccountId]),filter(Giving,[Total_Amount]>=1000&&[Total_Amount]<=9999),filter('Date','Date'[Date]<=_max && 'Date'[Date]>=-_min))