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.
Solved! Go to Solution.
Hi @Anonymous,
I modify calculated column formulas based on your expected result and sample data, please try to use it if it works:
Lost Date = VAR prevDate = CALCULATE ( MAX ( Lost[Visit_Date] ), FILTER ( ALL ( Lost ), [patid] = EARLIER ( [patid] ) && [Visit_Date] < EARLIER ( Lost[Visit_Date] ) ) ) RETURN IF ( DATEDIFF ( prevDate, [Visit_Date], MONTH ) >= 18, DATE ( YEAR ( prevDate ), MONTH ( prevDate ) + 18, DAY ( prevDate ) ) ) Reactive Date = VAR nextDate = CALCULATE ( MIN ( Lost[Visit_Date] ), FILTER ( ALL ( Lost ), [patid] = EARLIER ( Lost[patid] ) && [Visit_Date] >= EARLIER ( [Lost Date] ) ) ) RETURN IF ( [Lost Date] <> BLANK () && nextDate <> BLANK (), nextDate )
Regards,
Xiaoxin Sheng
Hi @Anonymous,
You can try to use following formulas, I use current user and date to find out previous/next date, then use diff between current and previous/next date as condition to mark them as lost or reactivated date.
Lost Date = VAR prevDate = CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( ALL ( 'Table' ), [User] = EARLIER ( 'Table'[User] ) && [Date] < EARLIER ( 'Table'[Date] ) ) ) RETURN IF ( DATEDIFF ( prevDate, [Date] , MONTH ) >= 18, prevDate ) Reactivated date = VAR nextDate = CALCULATE ( MIN ( 'Table'[Date] ), FILTER ( ALL ( 'Table' ), [User] = EARLIER ( [User] ) && [Date] > EARLIER ( 'Table'[Date] ) ) ) RETURN IF ( [Lost Date] <> BLANK (), IF ( DATEDIFF ( [Lost Date], nextDate, MONTH ) >= 18, nextDate ) )
Regards,
Xiaoxin Sheng
Hi @Anonymous,
Did you mean you want these calculate columns are dynamic based on those two columns?
For example, if one of formula find out the lost date, another one should use previous lost data to find out the correspondent reactivated date, then use reactivated date to calculate next lost date and so on?
If this is a case, I don't think it is possible to achieve your requirement. Power bi dax formulas not support to do recursive calculation. Maybe you can try to calculate them in excel side, it contains row index to do recursive calculation.
Regards,
Xiaoxin Sheng
Hi @Anonymous,
I modify calculated column formulas based on your expected result and sample data, please try to use it if it works:
Lost Date = VAR prevDate = CALCULATE ( MAX ( Lost[Visit_Date] ), FILTER ( ALL ( Lost ), [patid] = EARLIER ( [patid] ) && [Visit_Date] < EARLIER ( Lost[Visit_Date] ) ) ) RETURN IF ( DATEDIFF ( prevDate, [Visit_Date], MONTH ) >= 18, DATE ( YEAR ( prevDate ), MONTH ( prevDate ) + 18, DAY ( prevDate ) ) ) Reactive Date = VAR nextDate = CALCULATE ( MIN ( Lost[Visit_Date] ), FILTER ( ALL ( Lost ), [patid] = EARLIER ( Lost[patid] ) && [Visit_Date] >= EARLIER ( [Lost Date] ) ) ) RETURN IF ( [Lost Date] <> BLANK () && nextDate <> BLANK (), nextDate )
Regards,
Xiaoxin Sheng
Hi Sheng,
Could you help me with creation of a calculated column or custom column with the below as the logic
if( pat_id = earlier (pat_id),
if( datediff (Procdate, earlier (Procdate), month ) >18,
date( year( earlier(Procdate) ) , month (earlier(Procdate) )+ 18 , day( earlier(Procdate) ) ) ) ,
if( datediff ( Procdate, today(), day) > 548,
date( year( Procdate ) , month (Procdate )+ 18 , day( Procdate ) ) )
)
--- pat_id and Procdate are column of table
Regards,
KevinWH
Hi @Anonymous,
Hmm, so you mean if next visit date is large than 18 months, it should mark the correspond lost date at current row and mark next visit date as reactivated date?
Regards,
Xiaoxin Sheng
Hi Xiaoxin Sheng,
I understand it is bit complicated. I have attached a file. Please download it. It is a .csv which has sample date and explanation for lost and reactivated date.
http://www.filedropper.com/lostpatientandreactivatedpatientsampledata_1
Regards,
Ruban Leslie J
Hi @Anonymous
Could you post sample data (or the pbix)?
@Anonymous
You have to post here the URL to the file, either from platforms like OneDrive, Dropbox... or upload the file to sites like http://www.tinyupload.com/ or https://www.filedropper.com/ (no sign-in required) and share the URL
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |