ABC classification for 'same stock volume' since last 6/3/1 months.

[liberty20]

Hi,

I am trying to create a bucket where I need to classify Items in the groups of A/B/C on the basis of available stock quantity.

For Example :

If an Item no. 20048 is having same quantity since last 6 months then it should be label as 'A' ;

If having same quantity since last 3 months then 'B' ;

If having same quantity since last 1 month then 'C'

Thank you in advance!

[v-yangliu-msft]

Hi  @liberty20 ,

Are there other tables included in Visual, can you share your PBIX to delete sensitive data, we can help you better.

Best Regards,

Liu Yang

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

[liberty20]

Hi @v-yangliu-msft ,

We are referring to single table only.

I am just trying to highlight the products which are having same quantity (i.e. not sold) since last 6 months or since 3 months or since 1 month.

[v-yangliu-msft]

Hi  @liberty20 ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _date=MAXX(FILTER(ALL('Table'),'Table'[Item NO]=MAX('Table'[Item NO])),[Month-Year])
var _maxxdate=MONTH(MAXX(FILTER(ALL('Table'),'Table'[Item NO]=MAX('Table'[Item NO])),[Month-Year]))
var _1month=DATE(YEAR(_date),MONTH(_date)-1,1) //2022.7.1
var _3month=DATE(YEAR(_date),MONTH(_date)-2,1) //2022.6.1
var _6month=DATE(YEAR(_date),MONTH(_date)-5,1) //2022.3.1
var _sum1=SUMX(FILTER(ALL('Table'),'Table'[Item NO]=MAX('Table'[Item NO])&& 'Table'[Month-Year]>=_1month &&'Table'[Month-Year]<=EOMONTH(_date,0)),[Qty])
var _sum3=SUMX(FILTER(ALL('Table'),'Table'[Item NO]=MAX('Table'[Item NO])&& 'Table'[Month-Year]>=_3month&&'Table'[Month-Year]<=EOMONTH(_date,0)),[Qty])
var _sum6=SUMX(FILTER(ALL('Table'),'Table'[Item NO]=MAX('Table'[Item NO])&& 'Table'[Month-Year]>=_6month&&'Table'[Month-Year]<=EOMONTH(_date,0)),[Qty])
var _summax=SUMX(FILTER(ALL('Table'),'Table'[Item NO]=MAX('Table'[Item NO])&&'Table'[Month-Year]>=DATE(YEAR(_date),MONTH(_date),1)&&'Table'[Month-Year]<=EOMONTH(_date,0)),[Qty])
return
IF(
MOD(_sum6,_summax)=0,"A",
IF(
MOD(_sum3,_summax)=0,"B",
IF(
MOD(_sum1,_summax)=0,"C")
))

2. Result:

Best Regards,

Liu Yang

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

[liberty20]

Hi @v-yangliu-msft,

Many thanks for your quick responses!!

Followed the same steps and created measure successfully without any error, but in terms of ABC classification few results are not properly getting categorized.

Example:

[v-yangliu-msft]

Hi  @liberty20 ,

I created some data:

Here are the steps you can follow：

1. Create Measure.

Measure =
var _today=TODAY()
var _last1month=DATE(YEAR(_today),MONTH(_today)-1,DAY(_today))
var _last2month=DATE(YEAR(_today),MONTH(_today)-2,DAY(_today))
var _last3month=DATE(YEAR(_today),MONTH(_today)-4,DAY(_today))
var _last6month=DATE(YEAR(_today),MONTH(_today)-7,DAY(_today))
var _label1=SUMX(FILTER(ALL('Table'),'Table'[Item no]=MAX('Table'[Item no])&&'Table'[Date]>_last2month&&'Table'[Date]<=_last1month),[Amount])
var _label2=SUMX(FILTER(ALL('Table'),'Table'[Item no]=MAX('Table'[Item no])&&'Table'[Date]>_last3month&&'Table'[Date]<=_last1month),[Amount])
var _label3=SUMX(FILTER(ALL('Table'),'Table'[Item no]=MAX('Table'[Item no])&&'Table'[Date]>_last6month&&'Table'[Date]<=_last1month),[Amount])
var _group=SUMX(FILTER(ALL('Table'),'Table'[Item no]=MAX('Table'[Item no])&&'Table'[Date]>_last1month&&'Table'[Date]<=_today),[Amount])
return
IF(
_group=_label1,"A",
IF(
_group=_label2,"B",
IF(
_group=_label3,"C",1)))

2. Result:

Best Regards,

Liu Yang

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

[liberty20]

Thank you @v-yangliu-msft , but this solution is not working in my case.

Sample data:

Expected Result:

[amitchandak]

@liberty20 , Try a measure like 

ABC =
var _6 = CALCULATE(sumx(Values('Date'[Month]), calculate(if([MTD Sales] = [Last Mtd Sales],0,1)),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-6,MONTH))
var _3 = CALCULATE(sumx(Values('Date'[Month]), calculate(if([MTD Sales] = [Last Mtd Sales],0,1)),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH))
var _1 = if([MTD Sales] = [Last Mtd Sales],0,1)
return
Switch( True() ,
not(isblank(_6)) && _6 =0 , "A",
not(isblank(_3)) && _6 =0 , "B",
"C"
)

[liberty20]

Thank you @amitchandak, but this solution is not working in my case.