cancel
Showing results 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

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.

 Date Part 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])RETURNIF(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])RETURNIF(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).

 Date Part 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
Super User

MoM part.pbix

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

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

2 REPLIES 2
Super User

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

``````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 )``````

Super User

MoM part.pbix

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

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Announcements

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.

Power BI Monthly Update - February 2024

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

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

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
Top Kudoed Authors