Forum Discussion
SUMX with datediff for events in progress gives incorrect results
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:
--calculate delay days between Delayed Discharge Start Date OR Start of Period (whichever is later)
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.
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
2 Replies
- BeaBF
Super User
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
Thank you so much for your quick response, and for the code: worked perfectly first time!
Much appreciated. 😊
