Sum of Amount filtered by a recent month

[Anonymous]

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.

[v-yangliu-msft]

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

[sjoerdvn]

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,

[sjoerdvn]

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

[Anonymous]

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? 

[sjoerdvn]

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.

[Anonymous]

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?

[sjoerdvn · Accepted solution]

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

[Anonymous]

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