Count the common Item Codes which exists in the previous year row, in the same data table?

[sushstorm]

Hi, I am trying to count the items which exists in the current year, and also existed in the previous year. The sample data is given below. As you can see from the data, I would like to find out say in 2019, we had a total item count of 6, and those matching with 2018's item codes are 3. 

Sample Data
Item Code	Date	Sale Price
1	31/01/2017	$3
6	30/04/2017	$25
3	30/05/2017	$31
5	30/06/2017	$12
2	31/07/2017	$10
4	31/10/2017	$34
10	31/01/2018	$45
1	30/09/2018	$21
11	28/02/2018	$24
9	28/02/2018	$51
4	31/03/2018	$46
2	30/06/2018	$121
3	31/07/2018	$106
1	31/07/2019	$12
2	31/01/2019	$34
3	30/06/2019	$45
15	30/08/2019	$42
16	30/06/2019	$24
17	30/09/2019	$100
1	31/12/2020	$32
15	31/01/2020	$54
16	29/02/2020	$45
20	29/02/2020	$67
21	31/03/2020	$32
22	30/06/2020	$11

The final result, I would like to see is like this:

Year	Current Count	Matched with Next 12 Months
2017	6	4
2018	7	2
2019	6	3
2020	6	-

I'm able to do this in excel with VLOOKUP function, but not sure how to do it in Power BI.

2017	2018	2018 Item codes matching with 2017	2019	2019 Item codes matching with 2018	2020	2020 Item codes matching with 2019
1	3	3	1	#N/A	1	1
2	2	2	2	2	15	15
3	4	4	3	3	16	16
4	9	#N/A	15	#N/A	20	#N/A
5	10	#N/A	16	#N/A	21	#N/A
6	11	#N/A	17	#N/A	22	#N/A
	1	1

I have used the calculated column earlier, but it is not giving me the right results for each year going in the past Years.

Existing Item from Previous Year = 
var _count = 
COUNTROWS(
    FILTER(
        ALL('Sample Data'),
        [Item Code] = EARLIER('Sample Data'[Item Code]) &&
        'Sample Data'[Date] = YEAR(TODAY())-1 &&
    )
)
return
IF(
    ISBLANK(_count),
    0,
    1
)

Any help would be much appreciated!

Thanks

[sushstorm]

Hi @amitchandak, 

Thanks for your reply, but in this case, I'm getting the following message:

Last year Sales: FUNCTION 'DATEADD' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion.

Also, is there any chance I can get the view with Months and Years, instead of just years in the final results?

Thanks

[v-chenwuz-msft · Accepted solution]

Hi @sushstorm ,

Maybe you can try this code,this has the view of months by drill down:

Matched with Next 12 Months or next month =
VAR _cur_m_t = // current month
    GROUPBY(
        FILTER(
            ALL( 'Table' ),
            DATE( YEAR( [Date] ), MONTH( [Date] ), 1 )
                = DATE( SELECTEDVALUE( 'Table'[Date].[Year] ), SELECTEDVALUE( 'Table'[Date].[MonthNo] ), 1 )
        ),
        'Table'[Item Code]
    )
VAR _nex_m_t = // next month
    GROUPBY(
        FILTER(
            ALL( 'Table' ),
            DATE( YEAR( [Date] ), MONTH( [Date] ), 1 )
                = DATE( SELECTEDVALUE( 'Table'[Date].[Year] ), SELECTEDVALUE( 'Table'[Date].[MonthNo] ) + 1, 1 )
        ),
        'Table'[Item Code]
    )
VAR _cur_y_t = // current year
    GROUPBY(
        FILTER(
            ALL( 'Table' ),
            [Date].[Year] = SELECTEDVALUE( 'Table'[Date].[Year] )
        ),
        'Table'[Item Code]
    )
VAR _nex_y_t = // next year
    GROUPBY(
        FILTER(
            ALL( 'Table' ),
            [Date].[Year]
                = SELECTEDVALUE( 'Table'[Date].[Year] ) + 1
        ),
        'Table'[Item Code]
    )
VAR _match_m =
    COUNTROWS( _cur_m_t ) - COUNTROWS( EXCEPT( _cur_m_t, _nex_m_t ) )
VAR _match_y =
    COUNTROWS( _cur_y_t ) - COUNTROWS( EXCEPT( _cur_y_t, _nex_y_t ) )
RETURN
    IF( SELECTEDVALUE( 'Table'[Date].[Month] ) = BLANK(), _match_y, _match_m )

Result:

(2018 and 2019, they all have item code 1,but why you expect result is 2)

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[sushstorm]

Hello, 

Thanks for your solution. I had to tweek it a little bit to get to my solution, but it eventually worked! 

I changed the following section of your code:

VAR _nex_m_t = // next month GROUPBY( FILTER( ALL( 'Table' ), DATE( YEAR( [Date] ), MONTH( [Date] ), 1 ) = DATE( SELECTEDVALUE( 'Table'[Date].[Year] ), SELECTEDVALUE( 'Table'[Date].[MonthNo] ) + 12, 1 ) ), 'Table'[Item Code]

[amitchandak]

@sushstorm , With help from date table create meausre like first 2 or Next two

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

or

This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Then check for

countx(values(Table[Item Code]), if(not(isblank([This year Sales])) && not(isblank([Last year Sales])) , [Item Code], blank()))

Very similar logic

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...