Forum Discussion
Error on DateDiff Measure w/ Variables
Hello fellow at-home warriors, I am attempting to build a date different measure to account for a range of bad dates data that spans a couple decades in total. I know which line is creating the issu...
It is one of the attempts to correct for the data type, since I am uncertain as to how to get around it. I took shots in the dark.
HI DataUsurper,
As Greg_Deckler said, current format function only support to return text values, so you can't use it to convert text to number. (you will get number value format text values)
You can try to use the following measure formula if it suitable for your scenario:
Test Begin Date =
VAR billFrom =
MAX ( tbl_Orders_DatesDays[BILLFROM_DATE] )
VAR YrFrom =
YEAR ( billFrom )
VAR YrCreated =
YEAR ( MAX ( tbl_Orders_DatesDays[CREATED_DATE] ) )
VAR YrShp =
YEAR ( MAX ( tbl_Orders_DatesDays[SHIP_DATE] ) )
VAR _year =
IF (
ABS ( YrFrom - YrCreated - 5 ) > 5,
YrShp,
IF (
2 * YrCreated > YrShp + YrFrom,
YrCreated,
IF (
YrFrom < YrCreated
&& YrShp > YrCreated,
YrShp,
MIN ( YEAR ( TODAY () ) + 1, YrFrom )
)
)
)
RETURN
DATE ( _year, MONTH ( billFrom ), DAY ( billFrom ) )
If above not help, I'd like some dummy data to test. It is hard to test a Dax formula without any sample data.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng