Distinct account with last date and the days

[Karen1015]

Below is my table, TR.

Date	Account	Type	Balance
2023/1/10	A	Transfer	10
2023/5/18	A	Save	20
2023/5/19	A	Interest	30
2023/5/30	A	Payment	10
2023/2/7	B	Transfer	50
2023/3/8	B	Save	80
2023/3/19	B	Save	100
2023/4/16	B	Payment	20
2023/4/30	B	Interest	30

I want to filter the table by three rules.
1. The dates in the table must have a maximum of 15 days difference from 5/31.

2. Filter the type which doesn't equal "Interest" and "Payment".

3. Show the table with distinct accounts with a max date.

Date	Account	Type	Balance	Datediff
2023/5/18	A	Save	20	13

Below is my Dax. ( I make a measure in the Power BI dashboard.) But it always can't work. I want to count the account. (Show the value by a card)

total value=

var _t1 =

ADDCOLUMNS ( 'TR', "Datediff",  DATEDIFF ( 'TR'[date],  DATE ( 2023, 05, 31), DAY ) )

var _t2 =

MINX(FILTER( '_t1' , [account] = EARLIER ([account]) && [date] <= EARLIER([date] && [Datediff]<= 15 )), [Datediff])

return

    SUM('_t2'[account])

Thanks a lot.

[Thejeswar · Accepted solution]

Hi @Karen1015 ,

You can create a new measure using the DAX below

NewTR = 

VAR noofaccount = 
    SUMMARIZE (
        FILTER (
            TR,
            DATEDIFF ( TR[Date ], DATE ( 2023, 5, 31 ), DAY ) < 15
                && NOT ( TR[Type] ) IN { "Interest", "Payment" }
        ),
        TR[Date ],
        TR[Account ],
        TR[Type],
        TR[  Balance]
    )
RETURN
COUNTROWS(noofaccount)

You can also create a new table that is what you are looking for using the below DAX whose output looks like shown below

[vs_7]

HI  ,try below measure:

Total Value = VAR MaxDateAccounts = ADDCOLUMNS ( SUMMARIZE ( FILTER ( 'TR', 'TR'[Type] <> "Interest" && 'TR'[Type] <> "Payment" && DATEDIFF ( 'TR'[Date], DATE ( 2023, 05, 31 ), DAY ) <= 15 ), 'TR'[Account], "MaxDate", MAX ( 'TR'[Date] ) ), "Datediff", DATEDIFF ( [MaxDate], DATE ( 2023, 05, 31 ), DAY ) ) RETURN COUNTROWS ( MaxDateAccounts )

In this measure, we first filter the 'TR' table based on the given conditions: Type is not "Interest" or "Payment" and the date difference is less than or equal to 15. We then use the SUMMARIZE function to group the filtered data by 'Account' and calculate the maximum date for each account.

Next, we calculate the date difference using the 'MaxDate' and the specified date using the DATEDIFF function. Finally, we count the number of distinct accounts from the resulting table, which gives us the desired count.

@Karen1015