topic Re: Sum of Amount filtered by a recent month in DAX Commands and Tips

Sum of Amount filtered by a recent month

Anonymous — Thu, 01 Aug 2024 13:37:13 GMT

Hi,

I'm trying to come up with a measure, which will display value in a card visual, showing the sum of Amount_LC for the latest month available in the table, but at the same time I would like it to be compatible with Month slicer (Open_Items_Table[Month]).
This formula works correctly for summing the values for multiple months selected etc., but it shows the sum of all available months as a default, when no filters are used, but I would like it to display the sum for the recent month only (hence Max month used):

Value of Open Items =

VAR _LatestMonth = FILTER(Open_Items_Table, MAX(Open_Items_Table[Month]))

VAR _Amount = SUMX(_LatestMonth, Open_Items_Table[Amount_LC])

RETURN _Amount

Hope somebody could help, thanks.

Re: Sum of Amount filtered by a recent month

sjoerdvn — Thu, 01 Aug 2024 14:55:42 GMT

Value of Open Items = VAR _LatestMonth = MAX(Open_Items_Table[Month]) RETURN CACULATE(SUM(Open_Items_Table[Amount_LC]),Open_Items_Table[Month]=_LatestMonth)

Re: Sum of Amount filtered by a recent month

Anonymous — Thu, 01 Aug 2024 15:35:04 GMT

Hi, thanks for replying. That measure shows Max Month as a default, but when filtered by multiple months, it doesn't summarize for these months, is it possible to modify to show cumulative values?

Re: Sum of Amount filtered by a recent month

sjoerdvn — Thu, 01 Aug 2024 15:42:29 GMT

Well, this is all that I can guess with the information you provided. I would need more information to see what's going on and what you are trying to achieve.
I hope you used the code as a measure and not as a computed column.

Re: Sum of Amount filtered by a recent month

Anonymous — Thu, 01 Aug 2024 15:46:55 GMT

Yes, I used that as a measure and I achieved the same result as you previously, by using this dax:

Value of Open Items =

VAR _LatestMonth = MAX(Open_Items_Table[Month])

VAR _Filter = FILTER(Open_Items_Table, Open_Items_Table[Month] = _LatestMonth)

VAR _Amount = SUMX(_Filter, Open_Items_Table[Amount_LC])

RETURN _Amount
When unfiltered, it retrieves value for the recent month (6 in this case), when filtered by month 5, it will show total for May, but I would also want it to show values for both, when filtered by 5 & 6 at the same time, hope that makes it more clear?

Re: Sum of Amount filtered by a recent month

sjoerdvn — Thu, 01 Aug 2024 15:59:59 GMT

ok, try this

Value of Open Items = VAR _LatestMonth = MAX(Open_Items_Table[Month]) RETURN IF(ISFILTERED(Open_Items_Table[Month]) ,SUM(Open_Items_Table[Amount_LC]) ,CALCULATE(SUM(Open_Items_Table[Amount_LC]),Open_Items_Table[Month]=_LatestMonth) )

Re: Sum of Amount filtered by a recent month

Anonymous — Fri, 02 Aug 2024 05:46:26 GMT

Thanks for the reply from sjoerdvn , please allow me to provide another insight:
Hi Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Test = var _select=SELECTCOLUMNS('Open_Items_Table',"Month",[Month]) var _maxmonth=MAXX(ALL('Open_Items_Table'),[Month]) var _table={VALUE(_maxmonth)} var _column=UNION(_select,_table) var _count= CALCULATE(DISTINCTCOUNT('Open_Items_Table'[Month]),ALLSELECTED('Open_Items_Table')) return IF( _count=CALCULATE(DISTINCTCOUNT('Open_Items_Table'[Month]),ALL('Open_Items_Table'))&&HASONEVALUE('Open_Items_Table'[Month])=FALSE(), SUMX( FILTER(ALL('Open_Items_Table'), 'Open_Items_Table'[Month] =_maxmonth),[Amount_LC]) , SUMX( FILTER(ALL('Open_Items_Table'), 'Open_Items_Table'[Month] in _column),[Amount_LC]) )

2. Result:

No choice:

Select a month:

Select multiple months:

Best Regards,

Liu Yang

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

Re: Sum of Amount filtered by a recent month

Anonymous — Fri, 02 Aug 2024 08:55:10 GMT

Exactly what I needed, simple and brilliant! Thank you.

Re: Sum of Amount filtered by a recent month

sjoerdvn — Fri, 02 Aug 2024 15:36:23 GMT

Hi,
As a best practice, try to avoid SUMX or any "X" function if not really needed. If it cannot be avoided try not to iterate over entire tables, but restrict to single columns or aggregations.
SUMX over a million rows fact table will have a lousy performance.
As always with DAX, there are multiple ways of solving the issue above, but using SUMX here is just not a good idea.
Regards,