Count Created row and removed row compared with pervious data

[ChoiJunghoon]

Hi guy.

I am facing the big problem. please save me.

Table

Date	ID	Date index
2021-01-01	A	1
2021-01-01	B	1
2021-01-01	C	1
2021-01-02	A	2
2021-01-02	D	2
2021-01-02	E	2
2021-01-03	A	3
2021-01-03	D	3

I want 

Date	new Create row	Removed row
2021-01-01	3	0
2021-01-02	2	2
2021-01-03	0	1

2021-01-01 Created row : A & B & C  (3) /  Removed row : 0 

2021-01-02 Created row : D & E (2)         /  Removed : B & C (2) 

2021-01-03 Created row : 0                      /  Removed : E (1)

Could you make the DAX ?? 

[stevedep · Accepted solution]

Here you go:

NewRows = 
var _tblPrevDay   = CALCULATETABLE(VALUES('Table'[ID]), DATEADD(DateDim[Date].[Date], -1,DAY))
var _tblCurrent = VALUES('Table'[ID])
var _result = EXCEPT(_tblCurrent, _tblPrevDay)
return
COUNTROWS(_result)
// CONCATENATEX(_result, [ID] & "
// ") 

File is attached.

Kind regards, Steve. 

[ChoiJunghoon]

Thank you for your quick answer. 
Would you like to make the dax with "Date Index"? 
My Date column isn't generated every day.

[amitchandak]

@ChoiJunghoon , Create measures like this with help from date table

This Day = CALCULATE(count('Table'[ID]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))

Last Day = CALCULATE(count('Table'[ID]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))

Created ID = countx(values(Table[ID]), if(isblank([Last Day]) && not(isblank([This Day])),[ID], blank()))

removed Created ID = countx(values(Table[ID]), if(not(isblank([Last Day])) && isblank([This Day]),[ID], blank()))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.