Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
puzzle
Frequent Visitor

How to get the latest flag based on multiple rows and only display this

Hi there, I have an issue I am trying to resolve in my calculated column and am hoping someone can help?

 

Currently I am using a calculated DAX column to identify if someone is a new client based on if they have had a service in the last 6 months or not. It is working fine, however if someone has a new service, but then has a subsequent one afterwards then they are appearing twice as both new and existing.

 

What I would like to do is have this person in this scenario display as an existing client as they have had multiple services in the last 6 months. Example of what my table would currently look like below:

 

Person IDNew Client Flag
1Y
2N
2Y
3N
4Y

 

This is the DAX I am using in my calculated column:

 

New Client? =
VAR pers = 'Appended Event Data'[Person ID]
VAR datetocompare = 'Appended Event Data'[Event start date]
VAR disregard = 180
VAR Prev_LT_serv =
CALCULATE(COUNTROWS('Appended Event Data'),
    FILTER('Appended Event Data','Appended Event Data'[Person ID]=pers),
    FILTER('Appended Event Data',AND('Appended Event Data'[Event end date]>=datetocompare-disregard,'Appended Event Data'[Event start date]<datetocompare)),
    FILTER('Appended Event Data','Appended Event Data'[Service Length] = "Long Term"))
RETURN
    IF ( Prev_LT_serv>0, "N", "Y" )
 
Any assistance is much appreciated, and thanks in advance!
2 REPLIES 2
amitchandak
Super User
Super User

@puzzle , refer the new customer logic

Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

 

 

CALCULATE(COUNTROWS('Appended Event Data'),

@amitchandak 

 

Hi there, thanks for your reply.

I've given this a go, created 2 measures and a calculated column which doesn't seem to be working?

 

CLients accessing LT service = CALCULATE(COUNTROWS('Appended Event Data'),
                                FILTER('Appended Event Data','Appended Event Data'[Service Length] "Long Term"))
 
LT Service within the last 6 months? = CALCULATE([CLients accessing LT service],DATESINPERIOD(Dates[Date],ENDOFMONTH(Dates[Date]),-6,MONTH))
 
New Client V2 = CALCULATE([LT Service within the last 6 months?] > 1)
 
Expecting to see anyone with more than 1 Long term service in the last 6 months appear as "True" and therefore not a new client, however everyone is returning as false.
 
Any ideas?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors