DAX: Show This month's newly added/removed ID's compared to Last month

[Anonymous]

Hi, I have a list of active contracts every month, and curious how I can show the ID's of the contracts that were newly added and removed in this month compared with the month prior.

Current dataset:

Table A

ContractID, MonthYear

C1, 12/2019

C2, 12/2019

C3, 12/2019

C4, 12/2019

C1, 01/2020

C2, 01/2020

C3, 01/2020

C1, 02/2020

C2, 02/2020

C3, 02/2020

C5, 02/2020

Expected end result:

NULL, 12/2019, NULL

C4, 01/2020, Removed

C5, 02/2020, Added

Any suggestions if this is possible through DAX or Power Query, or is this better to fix in SQL?

[PaulDBrown · Accepted solution]

@Anonymous 

In DAX, here is one way (I've created a period table with a period index for the calculations)

Period Table:

 Model:

New Ids in month:

New this month = 
VAR Ids = VALUES('DataTable'[ContractID])
VAR LastMonthIDs = CALCULATETABLE(VALUES('DataTable'[ContractID]); 
FILTER(ALL('Period Table'); 
'Period Table'[Period Index] = SELECTEDVALUE('Period Table'[Period Index]) -1))
Return
COUNTROWS(
    EXCEPT(Ids; LastMonthIDs))

Lost Ids this month:

Lost this month = 
VAR Ids = VALUES('DataTable'[ContractID])
VAR LastMonthIDs = CALCULATETABLE(VALUES('DataTable'[ContractID]); 
FILTER(ALL('Period Table'); 
'Period Table'[Period Index] = SELECTEDVALUE('Period Table'[Period Index]) -1))
Return
COUNTROWS(
    EXCEPT(LastMonthIDs; Ids))

Which gets you this:

[Anonymous]

Works like a charm! For someone else using this solution, If you're wondering how to add an index per period in your periodtable dynamically, use the following calculated column:

Index = 
COUNTROWS(
    FILTER(VALUES('Calendar'[YearMonthnumber]);
    'Calendar'[YearMonthnumber] < EARLIER('Calendar'[YearMonthnumber])
))

[Mariusz]

Hi @Anonymous 

Please see the below PQ M script solution, I'm sure it's possible to achieve this using DAX or SQL. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjZU0lEyMNQ3NNI3MjC0VIrVAYoZYREzxiJmgkUMah4QGRkYGaCYhyJmjEUMptcIi14jLHqRxUzRxGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ContractID = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ContractID", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"ContractID", each _[ContractID], type list }}),
    #"Added nextDate" = Table.AddColumn(#"Grouped Rows", "nextDate", each Date.AddMonths( [Date], 1 ), type date),
    #"Merged Queries" = Table.NestedJoin(#"Added nextDate", {"Date"}, #"Added nextDate", {"nextDate"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"ContractID"}, {"next ContractID"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Grouped Rows", "result", 
        ( row ) => 
            if row[next ContractID] = null then { [ContractID = null, status = null] } else 
                let
                    removed = 
                        List.Transform( 
                            List.Difference( row[next ContractID], row[ContractID] ), 
                            (i) => [ContractID = i, status = "removed"]
                        ),
                    added = 
                        List.Transform( 
                            List.Difference( row[ContractID], row[next ContractID] ), 
                            (i) => [ContractID = i, status = "added"]
                        ),
                    combine = List.Combine( { removed, added } )
                in
                    combine 
    ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Date", "result"}),
    #"Expanded result" = Table.ExpandListColumn(#"Removed Other Columns", "result"),
    #"Expanded result1" = Table.ExpandRecordColumn(#"Expanded result", "result", {"ContractID", "status"}, {"ContractID", "status"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded result1",{{"ContractID", type text}, {"status", type text}})
in
    #"Changed Type1"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn