Count ids present two and three consequent days

[Anonymous]

Hi all,

I've the following datasource

Date	ID
01/03/2019	aaaaa
02/03/2019	aaaaa
03/03/2019	aaaaa
04/03/2019	bbbbb
05/03/2019	bbbbb
06/03/2019	bbbbb
07/03/2019	ccccc
08/03/2019	ccccc
09/03/2019	ccccc
10/03/2019	aaaaa
11/03/2019	aaaaa
12/03/2019	ccccc

I've to represent per date:

• the number of ids present in the date and the day before
• the number of ids present  in the date, in the day before and two days before

How to solve this in dax?

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

Create below calculated columns.

Previous date = LOOKUPVALUE(Test_5[Date],Test_5[ID],Test_5[ID],Test_5[Date],Test_5[Date]-1)
2 days before = LOOKUPVALUE(Test_5[Date],Test_5[ID],Test_5[ID],Test_5[Date],Test_5[Date]-2)

two consequent days = IF(Test_5[Previous date]<>BLANK(),"Yes","No")
three consequent days = IF(Test_5[Previous date]<>BLANK()&&[2 days before]<>BLANK(),"Yes","No")

Display  below measures into card visual.

count 2 days = CALCULATE(DISTINCTCOUNT(Test_5[ID]),Test_5[two consequent days]="Yes")
count 3 days = CALCULATE(DISTINCTCOUNT(Test_5[ID]),Test_5[three consequent days]="Yes")

Best regards,

Yuliana Gu

[Anonymous]

Thanks a lot.