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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
SophRow
Resolver II
Resolver II

Days since last event

Hi,

 

I have a table that contains a list of site names, participant ID at each site, and the date the participant was randomised into a clinical trial.

 

I want to develop a metric to show for each site the number of days since the most recent randomisation.

 

Example data:

 

SophRow_0-1674347151367.png

 

So in this example I'd want an output of '9 days' for site C, i.e., today minus 13/01/23.

 

Looking for the most efficient way to do this!

 

Thanks 🙂

Soph

1 ACCEPTED SOLUTION
SophRow
Resolver II
Resolver II

I've solved it!

 

If interested I used two measures:

 

Date of Last Randomisation =
CALCULATE ( MAX ( 'Participant Details'[Randomisation Date] ), ALLEXCEPT ( 'Participant Details', 'Participant Details'[Site Name] ) )
 
Days Since Last Randomisation = DATEDIFF('Participant Details'[Date of Last Randomisation],TODAY(),DAY)

View solution in original post

1 REPLY 1
SophRow
Resolver II
Resolver II

I've solved it!

 

If interested I used two measures:

 

Date of Last Randomisation =
CALCULATE ( MAX ( 'Participant Details'[Randomisation Date] ), ALLEXCEPT ( 'Participant Details', 'Participant Details'[Site Name] ) )
 
Days Since Last Randomisation = DATEDIFF('Participant Details'[Date of Last Randomisation],TODAY(),DAY)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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