Customize Filter guidance

[Bansi008]

Hi All,

I have a transaction data table with a huge number of records for several accounts. What I need is to identify which account + security ID is sold off in intra-month date. In the sample data below if we consider 03/07/2024 as month-end date then you probably see ACCOUNT_ID and SEC_ID 100 & 111 transactions are available from 1st to 7th March (considering 7th march is month-end). However, the same case is not with ACCOUNT_ID and SEC_ID 100 & 222 which is sold off on 03/03.

Is there a any way to just filter such records from the table using formula or DAX query? step wise guidance would be really helpful to handle such complex filter. 

Thanks!

[AnushaSri · Accepted solution]

Hi,

I have understood , you are basically try to ignore thos Acc and Sec Id's whic are sold off before month end, if so, you can filter the data using Power query.

I have slightly changed your example data as follows to have the end of month data 

Step-1 : create a duplicate of the table.
Step-2: on the duplicate table add all these steps in the advance editor 

#"Grouped Rows" = Table.Group(#"Changed Type", {"Account_id", "Sec_ID"}, {{"Maxdate", each List.Max([Date]), type nullable date}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Maxdate", "Maxdate - Copy"),
#"Calculated End of Month" = Table.TransformColumns(#"Duplicated Column",{{"Maxdate - Copy", Date.EndOfMonth, type date}}),
#"Added Conditional Column" = Table.AddColumn(#"Calculated End of Month", "Flag", each if [Maxdate] = [#"Maxdate - Copy"] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Flag] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Maxdate", "Maxdate - Copy", "Flag"})

Which will leave you only the following ACCOUNT_ID and Sec_ID which are not sold off intra month 

Step-3: Now go to original table and merge this table and create inner join as follows

#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Account_id", "Sec_ID"}, #"Sheet1 (2)", {"Account_id", "Sec_ID"}, "Sheet1 (2)", JoinKind.Inner),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Sheet1 (2)"})

And you see the filtered data.

Please accept this as solution and give kudos if it helps you solve your issue.
Thanks in advance