Forum Discussion

pawelj795's avatar
pawelj795
Post 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 be...
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]))
amitchandak's avatar
amitchandak
Super 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
    Post 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.
    • pawelj795's avatar
      pawelj795
      Post Prodigy
      5 years ago
      .