Measure: Count the number of distinct items in one time period that are in the previous.

[m_richardson]

I'm displaying some data that details the number of items for which there was a transaction on a monthly basis.  So, for the chart displayed below each bar is the number of distinct items in inventory that sold during each quarter.

The data above comes from a calculated measure that looks something like this:

Count items= DISTINCTCOUNT(Items[Item ID])

What I'd like to be able to do is to identify on a quarterly basis how many items in that quarter had also transacted the quarter prior.  What would be the best way of achieving this?

[v-yueyunzh-msft · Accepted solution]

Hi , @m_richardson 

According to your description, you want to calculate the "Count the number of distinct items in one time period that are in the previous". Right?

Here are the steps you can refer to :

(1)This is my test data:

(2)I create a calendar date table ,like this:

Date = ADDCOLUMNS( 
CALENDAR(Date(2021,1,1),Date(2022,12,31)),
"year", YEAR ( [Date] ),
"Quarter","Q" & ROUNDUP(MONTH([Date])/3,0),
"Year_Quarter", YEAR ( [Date] )*100+ ROUNDUP(MONTH([Date])/3,0)
)

(3)Then we can create a measure like this:

Measure = 
var _cur_Quarter = MAX('Date'[Year_Quarter])
var _previoud_Quarter =MAXX( FILTER(ALLSELECTED('Date') , 'Date'[Year_Quarter]<_cur_Quarter) , [Year_Quarter])
var _cur_Quarter_item_ID =VALUES('Item'[Item ID])
var _previoud_Quarter_item_ID = CALCULATETABLE( VALUES('Item'[Item ID]) , FILTER(ALLSELECTED('Date'),'Date'[Year_Quarter]=_previoud_Quarter))
var _inter = INTERSECT(_cur_Quarter_item_ID,_previoud_Quarter_item_ID)
return
COUNTROWS(_inter)

(4)Then we can mrrt your need , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[djurecicK2]

Hi @m_richardson ,

 Assuming you have a date table, you can you the PREVIOUSQUARTER function: 

https://learn.microsoft.com/en-us/dax/previousquarter-function-dax

Please accept as solution if this answers the question- thanks!

[m_richardson]

That won't give me what I want.

Let's say (for example) that in Q1 I had the following list of IDs of items that had sold:

10,11,12,13,14

And in Q2, this was the list:  10,13,14,22

Then what I'd want is for a way of telling me that in Q2 there were 3 IDs that had appeared in the previous quarter. 

Hope that makes sense.