New and deleted records per month

[Anonymous]

Hi,

I have this kind of data. 

month	subscription	resourcegroup	resourcename
2021-04	subs1	rg2	rn4
2021-04	subs1	rg2	rn5
2021-04	subs1	rg2	rn6
2021-05	subs1	rg1	rn1
2021-05	subs1	rg1	rn2
2021-05	subs1	rg1	rn3
2021-05	subs1	rg2	rn4
2021-05	subs1	rg2	rn5
2021-05	subs1	rg2	rn6
2021-06	subs1	rg1	rn1
2021-06	subs1	rg1	rn2
2021-06	subs1	rg1	rn3
2021-06	subs1	rg1	rn7

My goal is to show what resource groups and/or resources are being newly created or deleted each month. 

Target outputs:

RG-level (newly created)

month	resourcegroup	newRG
2021-04	rg2	TRUE
2021-05	rg2	FALSE
2021-05	rg1	TRUE
2021-06	rg1	FALSE

Resource-level (newly created)

month	resourcename	newResource
2021-04	rn4	TRUE
2021-04	rn5	TRUE
2021-04	rn6	TRUE
2021-05	rn4	FALSE
2021-05	rn5	FALSE
2021-05	rn6	FALSE
2021-05	rn1	TRUE
2021-05	rn2	TRUE
2021-05	rn3	TRUE
2021-06	rn1	FALSE
2021-06	rn2	FALSE
2021-06	rn3	FALSE
2021-06	rn7	TRUE

RG-level (deleted)

month	resourcegroup	delRG
2021-04	rg2	FALSE
2021-05	rg2	FALSE
2021-05	rg1	FALSE
2021-06	rg2	TRUE
2021-06	rg1	FALSE

Resource-level (deleted)

month	resourcename	delResource
2021-04	rn4	FALSE
2021-04	rn5	FALSE
2021-04	rn6	FALSE
2021-05	rn4	FALSE
2021-05	rn5	FALSE
2021-05	rn6	FALSE
2021-05	rn1	FALSE
2021-05	rn2	FALSE
2021-05	rn3	FALSE
2021-06	rn1	FALSE
2021-06	rn2	FALSE
2021-06	rn3	FALSE
2021-06	rn7	FALSE
2021-06	rn4	TRUE
2021-06	rn5	TRUE
2021-06	rn6	TRUE

I found this but in my case here, there is no related sales or cost.

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

I found this another one, but it does not work quite well compared to the solution from the previous link.

https://community.powerbi.com/t5/Desktop/Comaring-data-based-on-date/m-p/145820

For example, RG1 exists on 2021-03 and 2021-05, but not on 2021-04. It will not be shown as new RG in 2021-05, since it was existing or has previous records last 2021-03. 

Would like to have somehow similar logic with the 1st link, but I can't figure it out.

• New:  if resourcegroup/resourcename is NOT blank in current month and blank in the previous month
• Deleted: if resourcegroup/resourcename is blank in current month and NOT blank in the previous month

Thanks in advance for the help!

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

The below is what you want(the upper two is create newly,the below two is delete) :

Following is steps:

newly create:

rglevlenew =
IF (
    CALCULATE (
        MIN ( 'Table'[month] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[resourcegroup] = MAX ( 'Table'[resourcegroup] )
        )
    )
        = MAX ( 'Table'[month] ),
    TRUE (),
    FALSE ()
)

rsourcenew =
IF (
    CALCULATE (
        MIN ( 'Table'[month] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[resourcename] = MAX ( 'Table'[resourcename] )
        )
    )
        = MAX ( 'Table'[month] ),
    TRUE (),
    FALSE ()
)

Delete:

（group)

new table:

Table2 = CROSSJOIN(VALUES('Table'[month]),VALUES('Table'[resourcegroup]))

then create new column by the following dax:

iflastmonwithout =
LOOKUPVALUE (
    'Table'[month],
    'Table'[resourcegroup], Table2[resourcegroup],
    'Table'[month],
        DATE ( YEAR ( Table2[month] ), MONTH ( Table2[month] ) - 1, DAY ( Table2[month] ) ),
    0
)

ifthisonwithout =
LOOKUPVALUE (
    'Table'[month],
    'Table'[resourcegroup], Table2[resourcegroup],
    'Table'[month], Table2[month],
    0
)

pd = if(Table2[ifthisonwithout]=0&&Table2[iflastmonwithout]<>0,TRUE(),FALSE())

get the below:

resourcename:

Table3 = CROSSJOIN(VALUES('Table'[month]),VALUES('Table'[resourcename]))

iflastmonwithout = LOOKUPVALUE('Table'[month],'Table'[resourcename],Table3[resourcename], 'Table'[month],DATE(YEAR(Table3[month]),MONTH(Table3[month])-1,DAY(Table3[month])),0)

ifthisonwithout = LOOKUPVALUE('Table'[month],'Table'[resourcename],Table3[resourcename], 'Table'[month],Table3[month],0)

pd = if(Table3[ifthisonwithout]=0&&Table3[iflastmonwithout]<>0,TRUE(),FALSE())

Wish it is helpful for you!

Best Regards

Lucien

[amitchandak]

@Anonymous , In this blog chnage LMTD like

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529

all before this month

LMTD = calculate([Sales],Filter(All('Date'),'Date'[Date] <= eomonth(max('Date'[Date]),-1)))	

create date using month year

date = Date(left([Month Year],4), right([Month Year],2),1)

[Anonymous]

My month column is already marked as Date data type within the same table, so it already has the date hierarchy.

When I try to create a measure, it cannot detect automatically the column needed for the CALCULATE function. It only detects existing measures that I have already created before. I would like to not include the [Sales] or [Cost] in the logic, if possible.