Number of new unique ID's

[pandeyml]

Hey guys,

I have a [Date] and a [ID] column. There are more than 10k unique ID's coming in everyday. But there was a sudden rise in the number of ID's since few days. I want to get the [measure 1] number of new unique ID's which were not present yesterday and [measure 2] same with monthly. ID's which were not present a month before. I have a calendar table placed.

Please give me some ideas how to find unique id's ?

[Mikelytics]

Hi @pandeyml 

Can you please check the following appraoch fort measure 1?

my Base Table called "New_Count_ID"

measure for day calculation 

Measure 1 (Day) = 

var var_ReferenceDate = MAX(New_ID_Count[Date])
var var_DayBefore = var_ReferenceDate-1
var var_IDsReferenceDate = CALCULATETABLE(VALUES(New_ID_Count[ID]))
var var_CountOfIDsReferenceDate = COUNTROWS(var_IDsReferenceDate)
var var_IDsDayBefore = CALCULATETABLE(VALUES(New_ID_Count[ID]),New_ID_Count[Date]=var_DayBefore)


RETURN
var_CountOfIDsReferenceDate

-

COUNTROWS(
    NATURALINNERJOIN(var_IDsReferenceDate,var_IDsDayBefore)
)

Result

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[Mikelytics]

@pandeyml 

For measure 2 I extended my sample data set

and slightly adjsuted the measure

Measure 2 (Month) = 

var var_ReferenceDate = MAX(New_ID_Count[Date])
var var_ComparisonDate = EOMONTH(var_ReferenceDate,-1)
var var_IDsReferenceDate = CALCULATETABLE(VALUES(New_ID_Count[ID]))
var var_CountOfIDsReferenceDate = COUNTROWS(var_IDsReferenceDate)
var var_IDsComparisonDay = CALCULATETABLE(VALUES(New_ID_Count[ID]),New_ID_Count[Date]=var_ComparisonDate)


RETURN
var_CountOfIDsReferenceDate

-

COUNTROWS(
    NATURALINNERJOIN(var_IDsReferenceDate,var_IDsComparisonDay)
)

Result

Please be aware that the approach expüects that you have a list of IDs for every single day. Do you wirk with a data table or something or do you have dates without an ID list?

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[pandeyml]

Hey @Mikelytics ,

Appreciate your reply. It brings me closer to the exact solution.

the point that you mentioned in the end "expects a list of ID's coming everyday". I do have ID's coming everyday except for saturday and sunday. So when I use this measure it gives me all the unique ID's for monday. So eg : if there are 2000 unique ID's monday it gives me 2000 in the table. Rest of the days [tues - frid] it gives me correct values.

So my Data table includes

DATE	ID	Price 1	Warning Count
datetime	text	float	int (category value : 0,1,2)

Also,

Can I get the list of ID's that are new ? I'm trying but can't get the values please if you can help

[Mikelytics]

Hi @pandeyml 

Do you use a proper date table with weekday numbers and weekday descriptions? Can you please show me a screenshot of your data model?

Best regards

Michael

[pandeyml]

Calendar and Main table . Can See the ID column

Yes I do have the main calendar table.

[Mikelytics · Accepted solution]

Hi @pandeyml 

Can you please try the following

Measure 1 (Day) = 

var var_ReferenceDate = MAX(New_ID_Count[Date])
var var_ComparisonDate = 
        CALCULATE(
            LASTDATE(DimDate[Date]),
            FILTER(
                ALL(DimDate),
                DimDate[WeekDayNum]<> 6 && DimDate[WeekDayNum]<> 7 && DimDate[Date] < var_ReferenceDate 
            )
        )
var var_IDsReferenceDate = CALCULATETABLE(VALUES(New_ID_Count[ID]))
var var_CountOfIDsReferenceDate = COUNTROWS(var_IDsReferenceDate)
var var_IDsDayBefore = CALCULATETABLE(VALUES(New_ID_Count[ID]),New_ID_Count[Date]=var_ComparisonDate)


RETURN

var_CountOfIDsReferenceDate

-

COUNTROWS(
    NATURALINNERJOIN(var_IDsReferenceDate,var_IDsDayBefore)
)

Measure 2 (Month) = 

var var_ReferenceDate = MAX(New_ID_Count[Date])
var var_firstofMonth = EOMONTH(var_ReferenceDate,-1)
var var_ComparisonDate = 
        CALCULATE(
            LASTDATE(DimDate[Date]),
            FILTER(
                ALL(DimDate),
                DimDate[WeekDayNum]<> 6 && DimDate[WeekDayNum]<> 7 && DimDate[Date] < var_firstofMonth
            )
        )

var var_IDsReferenceDate = CALCULATETABLE(VALUES(New_ID_Count[ID]))
var var_CountOfIDsReferenceDate = COUNTROWS(var_IDsReferenceDate)
var var_IDsComparisonDay = CALCULATETABLE(VALUES(New_ID_Count[ID]),New_ID_Count[Date]=var_ComparisonDate)


RETURN
var_CountOfIDsReferenceDate

-

COUNTROWS(
    NATURALINNERJOIN(var_IDsReferenceDate,var_IDsComparisonDay)
)

in my case 6 and 7 are the weekend days

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[pandeyml]

Hi @Mikelytics ,

Thankyou very for your help. I do got the values this time. But I'm not sure if they are correct. Is there a way i can get the ID's name ?? A matrix doesn't show the values it just shows names of all ID's.

Regards,

Shubham

[sudhav]

lets say its yiour table

we need mainly 3 measures

1)CountofTotalUniqueIds = COUNT('IDs Table'[Unique ID])

2)countofUniqueIds_CurrentMonth = calculate(COUNT('IDs Table'[Unique ID]),FILTER('IDs Table','IDs Table'[Date].[MonthNo]=MONTH(TODAY())))

3)countofUniqueIds_Today = calculate(COUNT('IDs Table'[Unique ID]),FILTER('IDs Table','IDs Table'[Date].[Date]=TODAY()))

these all yoiu are expecting... let me know if anything else...

[pandeyml]

Thanks for your reply. I think this will give me the count of unique ID's per day and month. But what I need is the new unique ID's which were not present yesterday but are added today. So the ID's are repeated every day but today some more new ID's are added I want to get those bro. Same with the months. Which new ID's are added this month.

[sudhav]

Take a slicer and select relative date and select this month for the current month added IDs, then you will get list of this month added IDs which are not in the previous months

Another way is take a table visual and take unique IDS, date, countofUniqueIds_CurrentMonth for current month added IDs like below

For today added list of IDs which are not in yesterday, take same unique IDs, date,countofUniqueIds_Today in a table visual like below

I think it make sense..... please let me know if its useful to you and mark it as solution if you get answer...

[pandeyml]

I think there is a misunderstanding. Everyday 10k unique ID's are incomming means the same ID's are repeated everyday. But they are unique on a daily basis. Some new unique ID's get added today and I want to get only those.The example you have shown has different ID's everyday. So I'll have to mention in the measure that the ID(today) <> ID(yesterday).

Hope I'm clear. Let me know what I'm Missing