DAX Date Measure checks out OK yet does not work

[Ducati]

Please can I request some assistance in understanding my measure and why it is not working. I think the problem is the blank rows and it might be related to “row context”

Here is the scenario of what I am trying to resolve:

Patients are discharged from hospital with a reablement service and this service does end. The report is complied daily and therefore there will always be patients who are not yet discharged from the service.

So what I want to measure is the Length of Stay (LOS) in the reablement service. There are two key dates involved and the discharge date can be blank:

1. Reablement Start Date
2. HART discharge Date
3. Blank discharge date (patient not yet discharged)

LOS2 is the first measure that I developed which checks if the “discharge date” is BLANK and if it is the measure returns a 0 else the measure subtracts the reablement start date from the discharge date. This measure works fine and is halfway to solving the problem.

Measure LOS2
SUMX (
tbl_Data_Master_HART,
IF (
tbl_Data_Master_HART[HART discharge Date] = BLANK (),
0,
DATEDIFF (
tbl_Data_Master_HART[Reablement Start Date],
tbl_Data_Master_HART[HART discharge Date],
DAY
) + 1
)
)

Below is what the measure returns

When a patient is not yet discharged a 0 is no use, I need to know how long they have they been in the service so I need to use Today’s date and subtract the reablement start date

However, I get stuck when I try and replace the 0 in the logical statement with this calculation
DATEDIFF (
tbl_Data_Master_HART[Reablement Start Date],
TODAY(),
DAY
) + 1,

It doesn’t work and I suspect the problem may be related to the row context or the blanks

The full formula is:

SUMX (
tbl_Data_Master_HART,
IF (
tbl_Data_Master_HART[HART discharge Date] = BLANK (),
DATEDIFF (
tbl_Data_Master_HART[Reablement Start Date],
TODAY(),
DAY
) + 1,
DATEDIFF (
tbl_Data_Master_HART[Reablement Start Date],
tbl_Data_Master_HART[HART discharge Date],
DAY
) + 1
)
)

There are no errors in the formula checker and in the DAX Formatter does not report any bugs yet when I add the measure to the pivot table I get the following error message

[MK3010]

Hi @Ducati

Could you please share your sample file, it will be easy to help you.

Thanks
Mukesh

[Heather]

@Ducati

as @MK3010 said, it is always easier if we have a small sample dataset to work from, but here is what I have come up with that works the sample I quickly created

And, I think you will find the measure easier to read:

Test Measure = 
VAR Discharged = SELECTEDVALUE( Data[Discharge Date] )
VAR Reablement = SELECTEDVALUE( Data[Reablement Start Date] )
VAR FullyDischarged = DATEDIFF(Reablement, Discharged, DAY ) + 1
VAR inReablemenet = DATEDIFF( Reablement, TODAY(), DAY ) 
RETURN
    SWITCH( TRUE(),
    Discharged = BLANK(), inReablemenet,
    FullyDischarged )

eDNA Solution - handling blank dates wtih DATEDIFF.pbix (17.3 KB)

[Ducati]

Good Morning Heather thank you so much for the revised measure and I can see your design is much better. I have tried to use the measure but it will not recognise SELECTEDVALUE

Is there an alternative?

[Ducati]

Hi Again
Tried a different approach with this measure

MAXX (
tbl_Data_Master_HART,
IF (
ISBLANK ( tbl_Data_Master_HART[HART discharge Date] ),
DATEDIFF ( tbl_Data_Master_HART[Reablement Start Date], TODAY (), DAY ),
DATEDIFF (
tbl_Data_Master_HART[Reablement Start Date],
tbl_Data_Master_HART[HART discharge Date],
DAY
)
)
)

yet again while no error is detected and the syntax is okay the error message is still the same

What am I not understanding about working with dates and blank dates in the column?

[Heather]

I think we have reached the point where you need to upload a simplified version of your model.
There may be something in the model that needs to be addressed to make things work properly.

[Ducati]

Hi Heather

I have found a solution which seems to be working

MAXX (
tbl_Data_Master_HART,
IF (
ISBLANK ( tbl_Data_Master_HART[HART discharge Date] ),
INT ( TODAY () - tbl_Data_Master_HART[Reablement Start Date] +1 ),
INT ( tbl_Data_Master_HART[HART discharge Date] - tbl_Data_Master_HART[Reablement Start Date] + 1)
)
)

this is returning the correct numbers on a spot check i have just done but I need to confirm further

It works but please review and see if the logic etc is right

Andrew

[Heather]

I’m glad you were able to find a solution that worked for you @Ducati, as for checking efficiency in that solution - this is another thing that can be hard to do without seeing your model.

As has been stated multiple times in the forum before, it is sometimes necessary to see an example of your model to really validate the solution.

• Table connections
• The way other measures feeding into your measure work
• How a date table is optimized
• Is a value coming from a calculated column

These are just a few of the factors that can impact how a measure performs…

That being said, without being able to ‘see behind the curtain’, your measure appears to be reasonable.

[EnterpriseDNA]

Hi @Ducati, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[EnterpriseDNA]

Hi @Ducati, we’ve noticed that no response has been received from you since the 18th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[EnterpriseDNA]

Hi @Ducati, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!