Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Thanks in advance for the help!
Solved! Go to 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
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
@Anonymous , In this blog chnage LMTD like
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)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |