cancel
Showing results for
Did you mean:
Highlighted
emmyraldx Frequent Visitor

## Can anyone help Convert this calculated column to measure

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))))

Emmyraldx
1 ACCEPTED SOLUTION

Accepted Solutions v-cherch-msft
Microsoft

## Re: Can anyone help Convert this calculated column to measure

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2 v-cherch-msft
Microsoft

## Re: Can anyone help Convert this calculated column to measure

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
emmyraldx Frequent Visitor

## Re: Can anyone help Convert this calculated column to measure

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

Announcements #### New Topics Started Badges Coming  