Need help on this DAX error message

[adaxx]

My logic is this: 

If variable date2 is not blank for this each ID, then output date2.

If date2 is blank, then output a random number between 0 and the most recent month of column date1.

So the sample data is like this:

ID	date1	date2	output
1	2023-02-28	3	3
2	2023-05-31		5
3	2022-12-31	0	0
4	2023-04-30	1	1
5	2023-01-31	4	4
6	2023-03-31		5

This is my function:

MO = IF(ISBLANK('table'[Date1])=False,'table'[Date1],RANDBETWEEN(0,(max(month('table'[date2]))))), but it keeps giving me error: The MAX function only accepts a column reference as an argument.

Can someone help me with this? Thank you!!!

[danextian]

Hi @adaxx ,

MAX function accepts either a column

=
MAX ( table[column] )

or two scalar  values

=
MAX ( 1, 2 )

=
MAX ( MONTH ( table[date column] ), 100 )

This part of your formula retuns just ONE scalar value

=
MONTH ( 'table'[date2] )

Below is your formula modified so the syntax is correct but I am not sure what you are trying to achieve here. You also did not mention if it is a measure or a calc column

MO =
IF (
    ISBLANK ( 'table'[Date1] ) = FALSE,
    'table'[Date1],
    RANDBETWEEN ( 0, MONTH ( 'table'[date2] ) )
)

[v_mark]

Try this. You might need to tweak the calculated column measure a little bit to tailor fit your needs. 

Output Measure =

IF(

    NOT(ISBLANK('Table'[date2])),

    'Table'[date2],

    RAND() * EOMONTH(MAX('Table'[date1]), 0)

)