Max Date Function for Date/Time

[kbell6931]

When I run this DAX query, it functions almost perfectly.  I say almost because it works on date only, but i need it to work with the time that is in the date field as well.

This works

Previous Admission Date = if([CensusType] = 4,

VAR CurrentPatientNo = 'Table1'[PatientID]

VAR CurrentDischargeDate = 'Table1'[DateTime].[Date]

VAR PreviousAdmissionDate =

CALCULATE (

MAX( 'Table1'[ACH/CAH/APH Discharge Date].[Date] ),

FILTER (

'Table1',

'Table1'[ACH/CAH/APH Discharge Date].[Date] < CurrentDischargeDate

&& 'Table1'[PatientID] = CurrentPatientNo

)

)

RETURN

PreviousAdmissionDate)

But when i remove the .[Date] from each of the dates, so i can include time as well, it times out and will not complete the query.  it says not enough memory

This does not work for me.

Previous Admission Date = if([CensusType] = 4,

VAR CurrentPatientNo = 'Table1'[PatientID]

VAR CurrentDischargeDate = 'Table1'[DateTime]

VAR PreviousAdmissionDate =

CALCULATE (

MAX( 'Table1'[ACH/CAH/APH Discharge Date] ),

FILTER (

'Table1',

'Table1'[ACH/CAH/APH Discharge Date] < CurrentDischargeDate

&& 'Table1'[PatientID] = CurrentPatientNo

)

)

RETURN

PreviousAdmissionDate)

Any helpful thoughts would be greatly appreciated.  Thanks

[v-yuta-msft · Accepted solution]

@kbell6931 ,

If you remove .[date], the loop number will be very large which may cause memory exceeding. You should modify your model structure or reduce the date table rows. You can refer to the doc and blogs below about how to improve the performance:

Optimization guide for Power BI 

A comprehensive guide to Power BI performance tuning

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kbell6931]

Thank you for your response.  I reduced some of the rows and tweaked a couple optimization points and the query, although still a little slow, worked just as intended.

[Greg_Deckler]

Ditch your .[Date] 's

[kbell6931]

When I ditch the .[Date]'s it stops working completely.  After some time, it returns an error that there isn't enough memory to perform that function.