0
votes

The below table has 2 columns

enter image description here

Where Column A is a Date column and Column B is a Text column where some values are equal to "x" and some are blank.

I need to create an output column which based on the below formula

IF (
    AND ( ColumnA < EOMONTH ( ColumnA, 3 ), ( ColumnB = "x" ) ),
    EOMONTH ( ColumnA, 3 ),
    "-"
)

I have written the following DAX formula for it:

Output =
IF (
    AND (
        ColumnA
            < EOMONTH ( DATE ( YEAR ( ColumnA ), MONTH ( ColumnA ), DAY ( ColumnA ) ), 3 ),
         ( ColumnB = "x" )
    ),
    EOMONTH ( ColumnA, 3 ),
    "-"
)

I'm getting an error with this formula that NULL is not allowed in this context

Note: We can leave Blank in place of "x".

How do I write the correct DAX formula to achieve the above?

1
Isn't ColumnA always less than EOMONTH(ColumnA, 3)?Alexis Olson
Can you provide an example of your output table? Can you with words explain what is the goal with the column Output?Angelo Canepa
@AlexisOlson No, ColumnA is not always less than EOMONTH(ColumnA,3) as in ColumnA there is some blank value also which will be false in the condition. Blank date column is not present in the sample table.Pradeep Kumar
@AngeloCanepa Actually, ColumnB value is also calculated with a formula. There is a column ID having multiple rows. If current ID is not equal to next row ID value then ColumnB is equal to x otherwise NULL. Then, we are checking that if ColumnA value is less than EOMonth(ColumnA,3) and ColumnB is equal to "x" then Output is equal to EOMONTH(ColumnA,3)Pradeep Kumar
I'd suggest doing an ISBLANK check if blanks are what the condition is there for.Alexis Olson

1 Answers

0
votes

The problem with your calculation is that you are mixing different data types in the same column.

The Output column is handling a date data types with a text data types, that's why you are getting an error. The columns could only handle date or text but not both at the same time.

To fix your calculation your need to change your ELSE statement from "-" to BLANK()