Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
calcstuff
Regular Visitor

Comparing List Additions and Removals Month to Month

I've got a table from an SAP database that has part numbers and the months they were/are active.

 

Date  Part Number
2023-04-01     ABC1
2023-05-01     ABC2
2023-05-01     ABC3
2023-06-01     ABC1
2023-06-01     ABC2

 

I want a final table that shows how many part numbers were added and/or removed for each month, from the previous month.

 

DatePart Numbers Added        Part Numbers Removed
2023-04-01         1           null
2023-05-01         2             1
2023-06-01         1             1

 

I can create a table visualization to obtain the table above, and the new "Part Numbers Added" column is simply a sum from the following DAX: 

 

Part Numbers Added = 
VAR prev_rec =
LOOKUPVALUE(
'sap'[Part Number],
'sap'[Date],
DATE(YEAR('sap'[Date]), MONTH('sap'[Date]) - 1, 1),
'sap'[Part Number],
'sap'[Part Number]
)
RETURN
IF(ISBLANK(prev_rec),
1,
0
)

 

However, when I try and calculate the "Part Numbers Removed" (comparing to the previous month) I get the right values, but they lead in months by +1. Here's my DAX:

 

Part Numbers Removed = 
VAR next_rec =
LOOKUPVALUE(
'sap'[Part Number],
'sap'[Date],
DATE(YEAR('sap'[Date]), MONTH('sap'[Date]) + 1, 1),
'sap'[Part Number],
'sap'[Part Number]
)
RETURN
IF(ISBLANK(next_rec),
1,
0
)

 

 How do I adjust the logic to get part numbers removed (from last month, compared to this month)? Currently I get this, which is the part numbers removed from next month (compared to this month).

 

DatePart Numbers Added      Part Numbers Removed (expected)       Part Numbers Removed (obtained)
2023-04-01            1              null                    1
2023-05-01            2                 1                    1
2023-06-01            1                 1                    2
1 ACCEPTED SOLUTION

MoM part.pbix

 

A most common, fundamental use case of time intelligence function.

ThxAlot_0-1691865149348.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @calcstuff 
Please refer to attached sample file with the proposed solution

1.png

Part Numbers Added = 
VAR Current_Month = SELECTEDVALUE ( SAP[Date] )
VAR Previous_Month = EOMONTH ( Current_Month, - 2 ) + 1
VAR CurrentPartNumbers = VALUES ( SAP[  Part Number] )
VAR PreviousPartNumbers = 
    CALCULATETABLE (
        VALUES ( SAP[  Part Number] ), 
        SAP[Date] = Previous_Month 
    ) 
VAR NewPartNumbers = EXCEPT ( CurrentPartNumbers, PreviousPartNumbers )
RETURN
    COUNTROWS ( NewPartNumbers )
Part Numbers Removed = 
VAR Current_Month = SELECTEDVALUE ( SAP[Date] )
VAR Previous_Month = EOMONTH ( Current_Month, - 2 ) + 1
VAR CurrentPartNumbers = VALUES ( SAP[  Part Number] )
VAR PreviousPartNumbers = 
    CALCULATETABLE (
        VALUES ( SAP[  Part Number] ), 
        SAP[Date] = Previous_Month 
    )
VAR RemovedPartNumbers = EXCEPT ( PreviousPartNumbers, CurrentPartNumbers )
RETURN
    COUNTROWS ( RemovedPartNumbers )

 

 

MoM part.pbix

 

A most common, fundamental use case of time intelligence function.

ThxAlot_0-1691865149348.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors