How to get items added and removed on selection of month

[komald]

Hello,

I have following data.

On the basis of data i have create a visual which shows products added and removed by month.
if i select month feb , then it should show me comparison for last and current month i.e jan and feb month.

if product is present in last month but absent in current month then product status should be "removed"

if product is absent in last month but present in current month then product status should be "added"

if product is present in both last and current month then status should be "Remain"

month slicer

product_id for previous month | product_id for current month  | status


Thanks in advance

[v-zhangti · Accepted solution]

Hi, @komald 

You can try the following methods.

New Table:

Date = CALENDAR(DATE(2022,1,1),DATE(2022,4,30))

Month = MONTH([Date])

Measure:

Status = 
Var _Previousmonth = SELECTEDVALUE('Date'[Month])-1
Var _table=CALCULATETABLE(VALUES('Table'[Product ID]),FILTER(ALL('Table'),[Month]=_Previousmonth))
Var _N1=IF(SELECTEDVALUE('Table'[Product ID]) in _table,1,0)
Return
SWITCH(TRUE(),
_N1=0&&SELECTEDVALUE('Date'[Month])=SELECTEDVALUE('Table'[Month]),"Added",
_N1=1&&SELECTEDVALUE('Date'[Month])=SELECTEDVALUE('Table'[Month]),"Remain")

Previous month = IF(SELECTEDVALUE('Table'[Month])=SELECTEDVALUE('Date'[Month])-1,1,0)

Previous month status = 
Var _table=CALCULATETABLE(VALUES('Table'[Product ID]),FILTER(ALL('Table'),[Month]=SELECTEDVALUE('Table'[Month])+1))
Var _N1=IF(SELECTEDVALUE('Table'[Product ID]) in _table,1,0)
Return
SWITCH(TRUE(),
_N1=0&&SELECTEDVALUE('Date'[Month])=SELECTEDVALUE('Table'[Month])+1,"Removed",
_N1=1&&SELECTEDVALUE('Date'[Month])=SELECTEDVALUE('Table'[Month])+1,"Remain")

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.