Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
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
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |