Count values for a particular day but not if it occurred the day before

[Syk]

Hi! I need some help figuring out how to count each of the values (letters in this case) without counting the values that occurred the day before. Please see the screenshot to explain a little better 🙂

[ValtteriN]

Hi,

Here is one way to do this:
(my sample data is same as in your example)

Measure 21 =

var cdate = MAX('Table (8)'[Date])

var ldate = calculate(MAX('Table (8)'[Date]),ALL('Table (8)'[Date]),'Table (8)'[Date]<cdate)

var reduce =

COUNTROWS(

INTERSECT(

GROUPBY(

Filter(all('Table (8)'),'Table (8)'[Date]=ldate),'Table (8)'[Value])

, GROUPBY(

Filter(all('Table (8)'),'Table (8)'[Date]=cdate),'Table (8)'[Value])

))

return

COUNT('Table (8)'[Value])-reduce

The idea is to get two tables within a variable (reduce) and then compare them for matching values. The tables are constructed from current date (cdate) and lastdate (ldate). If this interect table's row count is more than 0 it is reduced from the count.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[tamerj1]

Hi @Syk 

Try this code

Count =
VAR CurrentTable =
SELECTCOLUMNS ( Table, "@Value", Table[Value] )
VAR DayBeforeTable =
CALCULATETABLE ( CurrentTable, Table[Date] = Table[Date] - 1 )
VAR ExceptTable =
EXCEPT ( DayBeforeTable, CurrentTable )
VAR Result =
COUNTROWS ( ExceptTable )
RETURN
Result

 

[Greg_Deckler · Accepted solution]

@Syk Try:

Measure = 
  VAR __Date = MAX('Table'[Date])
  VAR __DayBefore = __Date - 1
  VAR __Values = DISTINCT('Table'[Value])
  VAR __ValuesDayBefore = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),[Date]=__DayBefore),"Value",[Value])
RETURN
  COUNTROWS(EXCEPT(__Values,__ValuesDayBefore))