DAX measure that calculates the list of transactions whose SUM is greater and less than $X

[Syndicate_Admin]

Hello everyone

Let's say I have the following fact table and also a calendar table.

Date Account Description Amount
5/5/2023 123 Legal 100k
5/6/2023 123 Legal 200k
5/6/2023 124 Rates 25k
5/7/2023 124 Legal 100k
5/8/2023 123 Rates 50k
5/9/2023 124 Incoming Transfer (270k)

The goal is to return the list of transactions per account whose SUM is greater than 250k or less than -250k.

Let's say I selected a date range from 5/5/2023 to 5/7/2023 in my Slicer. The following output is expected:

Date Account Description Amount
5/5/2023 123 Legal 100k
5/6/2023 123 Legal 200k

If for example I selected the range 5/7/2023 to 5/9/2023 my expected result would be BLANK given the following:

5/7/2023 124 Legal 100k
5/8/2023 123 Rates 50k
5/9/2023 124 Incoming Transfer (270k)

Account 124 sum ($170k)

Count 123 sum 50k

I have this Dax formula but it excludes transactions less than 250k and I need the GROUP of transactions that contribute to the condition per account.

FilteredTransactions =
VAR TotalTransactions = SUM('Cash Activity'[Transaction])
RETURN
IF(
OR(
TotalTransactions > 250000,
TotalTransactions < -250000
),
'Cash Activity'[Transactions],
BLANK()
)

Any idea how to achieve this?

Thanks in advance,

[sevenhills]

Try this 

Accounts Matching = 
var summaryTable = ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Table'),'Table'[Account], "sumamt",
   CALCULATE(SUM('Table'[Amount]))), "MatchCheck", [sumamt] >= 250000 || [sumamt] <= -250000)

var c = COUNTROWS(summaryTable) -- Testing! ... RETURN C 

return if ( HASONEVALUE('Table'[Account])  
    , If( CONTAINS(summaryTable, [MatchCheck], TRUE(), 'Table'[Account], SELECTEDVALUE('Table'[Account])), 1, 0)
    , blank() )

Add filter for your needs

Sample scenarios:

Hope this helps!

[foodd]

How to Get Your Question Answered Quickly