SUMX with datediff for events in progress gives incorrect results

[timbad]

Hi,

I'm trying to calculate delayed discharge days within each month for delayed discharges in progress during that month.

The maximum delay within a given month for a specific record is the length of the month e.g. 31 days in August.

I have this DAX measure which isn't returning the correct values for what I need:

Delayed Discharge Days Snapshot ALL =
--calculate delay days between Delayed Discharge Start Date OR Start of Period (whichever is later)

--and Delayed Discharge End Date OR End of Period (whichever is earlier)

VAR SOP = MIN('Date'[Date])

VAR EOP = MAX('Date'[Date])

RETURN

CALCULATE(

SUMX(

                    'Delayed Discharge (CRFD)',

DATEDIFF(

IF(

'Delayed Discharge (CRFD)'[IPDelayedDischargeStartDate] > SOP,

'Delayed Discharge (CRFD)'[IPDelayedDischargeStartDate],

SOP

),

IF(

'Delayed Discharge (CRFD)'[IPDelayedDischargeEndDate] < EOP,

                            'Delayed Discharge (CRFD)'[IPDelayedDischargeEndDate],

EOP

),

DAY

) + 1

),

'Delayed Discharge (CRFD)'[IPDelayedDischargeStartDate] <= EOP,

'Delayed Discharge (CRFD)'[IPDelayedDischargeEndDate] >= SOP || 'Delayed Discharge (CRFD)'[IPDelayedDischargeEndDate] = BLANK() ,

                REMOVEFILTERS( 'Date' )

)

I checked all the tables concerned and all relationships are correct, date table is fine, etc.
However, if I run a similar query in SQL I get 1077 days for August, but the above measure only returns 342 for the same month.

I'm guessing that it's something to do with the filter or row context, but can't work out how to fix it.

What am I doing wrong, please?

P.S. apologies, but since I work with confidential data, I am unable to upload my PBIX file or a dataset.

[BeaBF · Accepted solution]

@timbad it's difficult without the pbix file, if you can share some sample data it'd better. However, try with this formula:

Delayed Discharge Days Snapshot ALL =

VAR SOP = MIN('Date'[Date])
VAR EOP = MAX('Date'[Date])

RETURN
CALCULATE(
SUMX(
'Delayed Discharge (CRFD)',
VAR DischargeStart = 'Delayed Discharge (CRFD)'[IPDelayedDischargeStartDate]
VAR DischargeEnd = COALESCE('Delayed Discharge (CRFD)'[IPDelayedDischargeEndDate], EOP)
VAR EffectiveStart = MAX(DischargeStart, SOP)
VAR EffectiveEnd = MIN(DischargeEnd, EOP)
VAR DaysInMonth = DATEDIFF(EffectiveStart, EffectiveEnd, DAY) + 1
RETURN
IF(DaysInMonth > 0, DaysInMonth, 0)
),
'Delayed Discharge (CRFD)'[IPDelayedDischargeStartDate] <= EOP,
('Delayed Discharge (CRFD)'[IPDelayedDischargeEndDate] >= SOP || ISBLANK('Delayed Discharge (CRFD)'[IPDelayedDischargeEndDate])),
REMOVEFILTERS('Date') 
)

BBF

[timbad]

Thank you so much for your quick response, and for the code: worked perfectly first time!

Much appreciated. 😊