Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all
I'm hoping someone can help with this. My dax expression is:
Solved! Go to Solution.
There might be a way to make this quite a bit simpler.
Is this logic correct?
Column =
VAR StartDate = 'Admissions and Discharges'[DateAdmitted]
VAR EndDate =
MIN ( 'Admissions and Discharges'[DCDateForOBD], EOMONTH ( StartDate, 0 ) )
RETURN
DATEDIFF ( StartDate, EndDate, DAY )
Hi @MIrlam
Try this:
Column =
IF(
YEAR( 'Admissions and Discharges'[DateAdmitted] )
& MONTH( 'Admissions and Discharges'[DateAdmitted] )
>= YEAR( 'Admissions and Discharges'[DCDateForOBD] )
& MONTH( 'Admissions and Discharges'[DCDateForOBD] ),
DATEDIFF(
'Admissions and Discharges'[DateAdmitted],
'Admissions and Discharges'[DCDateForOBD],
DAY
),
DATEDIFF(
'Admissions and Discharges'[DateAdmitted],
ENDOFMONTH( 'Admissions and Discharges'[DateAdmitted].[Date] ),
DAY
)
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
There might be a way to make this quite a bit simpler.
Is this logic correct?
Column =
VAR StartDate = 'Admissions and Discharges'[DateAdmitted]
VAR EndDate =
MIN ( 'Admissions and Discharges'[DCDateForOBD], EOMONTH ( StartDate, 0 ) )
RETURN
DATEDIFF ( StartDate, EndDate, DAY )
That is brilliant, thank you!
Hi @MIrlam
Try this:
LOS Days (AdmissionMonth) =
IF(
YEAR( 'Admissions and Discharges'[DateAdmitted] )
&& MONTH( 'Admissions and Discharges'[DateAdmitted] )
>= YEAR( 'Admissions and Discharges'[DCDateForOBD] )
&& MONTH( 'Admissions and Discharges'[DCDateForOBD] ),
DATEDIFF(
'Admissions and Discharges'[DateAdmitted],
'Admissions and Discharges'[DCDateForOBD],
DAY
),
DATEDIFF(
'Admissions and Discharges'[DateAdmitted],
ENDOFMONTH( 'Admissions and Discharges'[DateAdmitted].[Date] ),
DAY
)
)
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Thank you, that has helped with the Jan / Dec overlap as highlighted below. However I now have an issue where the datediff isn't calculating as I think it should? As far as I can see the dax is correct but i get this return when the months for dateadmitted and d/cdateforOBD are the same
Column = IF( YEAR( 'Admissions and Discharges'[DateAdmitted] ) && MONTH( 'Admissions and Discharges'[DateAdmitted] ) >= YEAR( 'Admissions and Discharges'[DCDateForOBD] ) && MONTH( 'Admissions and Discharges'[DCDateForOBD] ), DATEDIFF( 'Admissions and Discharges'[DateAdmitted], 'Admissions and Discharges'[DCDateForOBD], DAY ), DATEDIFF( 'Admissions and Discharges'[DateAdmitted], ENDOFMONTH( 'Admissions and Discharges'[DateAdmitted].[Date] ), DAY ) )
Thanks
Hi @MIrlam
Try this:
Column =
IF(
YEAR( 'Admissions and Discharges'[DateAdmitted] )
& MONTH( 'Admissions and Discharges'[DateAdmitted] )
>= YEAR( 'Admissions and Discharges'[DCDateForOBD] )
& MONTH( 'Admissions and Discharges'[DCDateForOBD] ),
DATEDIFF(
'Admissions and Discharges'[DateAdmitted],
'Admissions and Discharges'[DCDateForOBD],
DAY
),
DATEDIFF(
'Admissions and Discharges'[DateAdmitted],
ENDOFMONTH( 'Admissions and Discharges'[DateAdmitted].[Date] ),
DAY
)
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
perfect, works now thanks!
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
19 | |
15 |