Can anyone help Convert this calculated column to measure

[emmyraldx]

Hi guys, I have wanting to convert this calculated column to measure: 

IF(AND(AND(FctTable[CSDateTime] <> BLANK(), FctTable[CSDateTime] <= FctTable[EndDate]), FctTable[CSDateTime]<TODAY()), 1, IF(FctTable[CSDateTime] >=TODAY(), 2, IF( AND(FctTable[CSDateTime] > FctTable[EndDate], FctTable[CSDateTime] <=TODAY()), 3, IF(FctTable[CSDateTime] = BLANK(), 0))))

This is the closest I have but it's still incorrect:

IF(AND(AND(LEFT(MAX('FctTable'[CSDateTime]),20) <> BLANK(), LEFT(MAX('FctTable'[CSDateTime]),20) <=LEFT(MAX

('FctTable'[EndDate]),20)), LEFT(MAX('FctTable'[CSDateTime]),20) < LEFT(MAX(TODAY(),20))), 1,

IF(LEFT(MAX('FctTable'[CSDateTime]),20) >= LEFT(MAX(TODAY(),20)), 2,

IF( AND(LEFT(MAX('FctTable'[CSDateTime]),20) > LEFT(MAX('FctTable'[EndDate]),20), LEFT(MAX('FctTable'[CSDateTime]),20) <= LEFT(MAX(TODAY(),20))), 3,

IF(LEFT(MAX('FctTable'[CSDateTime]),20) = BLANK(), 0))))

Please help.

Emmyraldx

[v-cherch-msft · Accepted solution]

Hi @emmyraldx 

You may try below measure:

Measure =
IF (
    AND (
        AND (
            MAX ( 'FctTable'[CSDateTime] ) <> BLANK (),
            MAX ( 'FctTable'[CSDateTime] ) <= MAX ( 'FctTable'[EndDate] )
        ),
        MAX ( 'FctTable'[CSDateTime] ) < TODAY ()
    ),
    1,
    IF (
        MAX ( 'FctTable'[CSDateTime] ) >= TODAY (),
        2,
        IF (
            AND (
                MAX ( 'FctTable'[CSDateTime] ) > MAX ( 'FctTable'[EndDate] ),
                MAX ( 'FctTable'[CSDateTime] ) <= TODAY ()
            ),
            3,
            IF ( MAX ( 'FctTable'[CSDateTime] ) = BLANK (), 0 )
        )
    )
)

Regards,

[emmyraldx]

@v-cherch-msft 

Thank you v-cherch. It works. Really appreciate.