Need help to get count of new and deleted ids for each Date

[ganes]

I have a table called mytable with an 2 colum Report_Date and id.
Each day few new ids gets created and few gets deleted.
I want to get the count of ids that are newly added for each date in the Report_Date colum
I want to get the count of ids that are deleted for each date n the Report_Date colum
I want to get the remaining unique count of ids that are still present for each date n the Report_Date colum

For example lets take an example : we have entries in the mytable as below

i want below output in Dax

[johnt75 · Accepted solution]

Create a proper date table, marked as a date table, and you can create measures like

Count new ids =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR CurrentIDs =
    VALUES ( 'Table'[id] )
VAR YesterdayIDs =
    CALCULATETABLE ( VALUES ( 'Table'[id] ), 'Date'[Date] = ReferenceDate - 1 )
VAR Result =
    COUNTROWS ( EXCEPT ( CurrentIDs, YesterdayIDs ) )
RETURN
    Result

Count deleted ids =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR CurrentIDs =
    VALUES ( 'Table'[id] )
VAR YesterdayIDs =
    CALCULATETABLE ( VALUES ( 'Table'[id] ), 'Date'[Date] = ReferenceDate - 1 )
VAR Result =
    COUNTROWS ( EXCEPT ( YesterdayIDs, CurrentIDs ) )
RETURN
    Result

Total IDs = COUNTROWS('Table')

[ganes]

Hi  @johnt75 ,

Thanks for the solution it is working for 2 consecutive dates but Not working for historical date.
Can you please guide me how to make it work for previous dates and Future Dates. 

Basically I want to show a Line Chart to represnt count of new ids and deleted Ids week wise and month wise

[johnt75]

I think you'd need separate measures for weekly and monthly. You could have the monthly like

Count new ids monthly =
VAR CurrentIDs =
    VALUES ( 'Table'[id] )
VAR YesterdayIDs =
    CALCULATETABLE ( VALUES ( 'Table'[id] ), DATEADD ( 'Date'[Date], -1, MONTH ) )
VAR Result =
    COUNTROWS ( EXCEPT ( CurrentIDs, YesterdayIDs ) )
RETURN
    Result

and for weekly just change -1, MONTH, to -7, DAY.

[ganes]

Hi @johnt75 ,

Thank you very much. This worked. Previously it was not working because data was inconsitent. 

[ganes]

Hi @johnt75 , Thanks again for sharing the query. However, it is only giving result for recent date and not for all the dates in the Report_Date colum. My requirement is to get each date in the Report_Date colum in mytable.

for eg. - in the attached image you can find the value is comming only for March 17 and not for previous dates. Also, the for previous dates the new ids showing total count of ids for that date not ones which got added newly from previous date.

Will be gratefull if you can share the logic to get all the dates vaule

[johnt75]

I'm not sure I fully understand the problem. You have a table with data for non-consecutive dates. When you plot, e.g. the number of new ids on a monthly or weekly chart, what exactly are you expecting to see? The number of ids which existed at the end of the period but did not exist at the end of the previous period ? Or some sort of daily aggregation ?

[ganes]

Hi @johnt75,
yes, my requirement is to show the count of ids that were deleted and newly added at the end of period. I have tried to explain my requirement in the screenshot attached. hope you got some clarrity

[johnt75]

You'll need a date table, marked as a date table, which you will use in all visuals. You can use week commencing, week ending or something similar as the column in your visuals.

Then you can create a measure like

Weekly new ids =
VAR EndCurrentWeek =
    MAX ( 'Date'[Date] )
VAR EndPrevWeek = EndCurrentWeek - 7
VAR LastDateThisWeek =
    CALCULATE (
        MAX ( 'Table'[Reporting date] ),
        DATESBETWEEN ( 'Date'[Date], EndPrevWeek + 1, EndCurrentWeek )
    )
VAR LastDateLastWeek =
    CALCULATE (
        MAX ( 'Table'[Reporting date] ),
        DATESBETWEEN ( 'Date'[Date], EndPrevWeek - 6, EndPrevWeek )
    )
VAR PrevIDs =
    CALCULATETABLE ( VALUES ( 'Table'[id] ), 'Date'[Date] = LastDateLastWeek )
VAR CurrentIDs =
    CALCULATETABLE ( VALUES ( 'Table'[id] ), 'Date'[Date] = LastDateThisWeek )
RETURN
    COUNTROWS ( EXCEPT ( CurrentIDs, PrevIDs ) )

which gets the last date for which you have data in the current week and the previous week, and compares the ids from those dates.

[ganes]

Hi @johnt75 ,

Thanks again for the response. However, I tried to follow whatever you advised but it is still not returning as per the requirement.
It is returning only id count for the date. But not how many id deleted and new id created from previous date.

Requirement is to get the values as shown below for March 17.

Similarly need values for other dates Mar 07, Mar 09, Mar 13, Mar 16 and future dates as well.

This is the table I created as per your advise 

Date = CALENDAR(DATE(2023,1,1), DATE(2099,12,31))

This is the new measure :

Weekly new id =

VAR EndCurrentWeek =

    MAX ( 'Date'[Date] )

VAR EndPrevWeek = EndCurrentWeek - 7

VAR LastDateThisWeek =

    CALCULATE (

        MAX ( 'mytable'[Report_Date]),

        DATESBETWEEN ( 'Date'[Date], EndPrevWeek + 1, EndCurrentWeek )

    )

VAR LastDateLastWeek =

    CALCULATE (

        MAX ( 'mytable'[Report_Date]),

        DATESBETWEEN ( 'Date'[Date], EndPrevWeek - 6, EndPrevWeek )

    )

VAR PrevIDs =

    CALCULATETABLE ( VALUES ( 'mytable'[id]), 'Date'[Date] = LastDateLastWeek )

VAR CurrentIDs =

    CALCULATETABLE ( VALUES ( 'mytable'[id] ), 'Date'[Date] = LastDateThisWeek )

RETURN

    COUNTROWS ( EXCEPT ( CurrentIDs, PrevIDs ) )

 I am updating the mytable.pbix file link here for more clarity.

https://onedrive.live.com/?authkey=%21AC7wjU%5FAXMZtThA&cid=82C96F93A7FA75E9&id=82C96F93A7FA75E9%211...