Forum Discussion

pawelj795's avatar
pawelj795
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Count Items which occurs again

Hi,
I need to create 2 measures.

  • The first one should count how many items occur again today.
  • The second one should tell how many times this particular item occur in the past.

 

Sample data below:

DateItem ID
02.10.2020 (today)

A

02.10.2020B
02.10.2020C
01.10.2020B
28.09.2020C
27.09.2020C
Need Help
Tips and Tricks
  • v-xuding-msft's avatar
    v-xuding-msft
    5 years ago

    Hello @pawelj795 ,

    Please try this:

    Measure 1 = 
var t1_= 
    CALCULATETABLE (
        VALUES ( 'Table'[Item ID] ),
        FILTER (
            ALL ( 'Table'[Date] ),
            'Table'[Date] < MAX('Table'[Date]) )
        )
var t2_= 
    CALCULATETABLE (
        VALUES ( 'Table'[Item ID] ),
        FILTER (
            ALL ( 'Table'[Date] ),
            'Table'[Date] = MAX('Table'[Date]) )
        )
return
COUNTROWS(INTERSECT(t1_,t2_))
    Measure 2 = CALCULATE(COUNT('Table'[Date]),ALLEXCEPT('Table','Table'[Item ID]))

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    pawelj795 ,

    The information you have provided is not making the problem clear to me. Can you please explain with an example.

    These can be two measures what I can get from a high level

     

    calculate(distinctcount(Table[Item ID]), Table[Date] =today())

    calculate(distinctcount(Table[Item ID]), Table[Date] < today())


    Appreciate your Kudos.

    • pawelj795's avatar
      pawelj795
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      Hi amitchandak 

      Alluding to my above sample, I explain what I meant.

       

      • First measure:
        The result should be 2, because only item "B" and item "C" appear again today.
      • Second measure:
        For the Item "B" the result should be 2. (also include today) - It appeared on 02.10.2020 and 01.10.2020
        For the Item "C" the result should be 3. - It appeared on 02.10.2020, 28.09.2020, and 27.09.2020.

        If it's still not clear to you, please tell me which part is confusing.
  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hello @pawelj795 ,

    Please try this:

    Measure 1 = 
var t1_= 
    CALCULATETABLE (
        VALUES ( 'Table'[Item ID] ),
        FILTER (
            ALL ( 'Table'[Date] ),
            'Table'[Date] < MAX('Table'[Date]) )
        )
var t2_= 
    CALCULATETABLE (
        VALUES ( 'Table'[Item ID] ),
        FILTER (
            ALL ( 'Table'[Date] ),
            'Table'[Date] = MAX('Table'[Date]) )
        )
return
COUNTROWS(INTERSECT(t1_,t2_))
    Measure 2 = CALCULATE(COUNT('Table'[Date]),ALLEXCEPT('Table','Table'[Item ID]))