What will be the quicksight calculation for the below Power BI measure ?
IF
( pqAgreements.[Agreement - Status] <> ‘40’ OR pqAgreements.[Agreement Line - Status] <> '20 )
AND ( TODAY() BETWEEN pqAgreements.[Agreement Line - Valid From] AND pqAgreements.[Agreement Line - Valid To] AND TODAY() BETWEEN pqAgreements.[Agreement - Valid From] AND pqAgreements.[Agreement - Valid To]
)
THEN ‘Current’
ELSEIF
( pqAgreements.[Agreement - Status] <> ‘40’ OR pqAgreements.[Agreement Line - Status] <> '20 )
AND ( TODAY() <= pqAgreements.[Agreement Line - Valid From] AND TODAY() <= pqAgreements.[Agreement - Valid From] )
THEN ‘Future’
ELSE ‘Expired/Inactive’
Hi @Hemant_rangdal
it would be more or less the same. Just a few changes.
Ifelse(comparison1,“current”, comparison2,“future”,“expired”)
You have to replace the “between” with “> and <”
Today will be now().
BR
1 Like
Will is that correct ?
“ifelse({agreement - status}<> ‘40’ OR {agreement line - status}<> ‘20’ AND now()< {agreement line - valid from} AND {agreement line - valid to}AND now()> {agreement - valid from} AND {agreement - valid to}, ‘Current’, {agreement - status}<> ‘40’ OR {agreement line - status}<> ‘20’ AND now()<= {agreement line - valid from} and now()<= {agreement - valid from}, ‘Future’, ‘Expired/Inactive’)”
Hi @Hemant_rangdal
Did you try it?
BR
yes but its not working
you are missing a few comparisons.
“ifelse({agreement - status}<> ‘40’ OR {agreement line - status}<> ‘20’ AND now()< {agreement line - valid from} AND {agreement line - valid to}AND now()> {agreement - valid from} AND **??** {agreement - valid to}, ‘Current’, {agreement - status}<> ‘40’ OR **??** {agreement line - status}<> ‘20’ AND now()<= {agreement line - valid from} and now()<= {agreement - valid from}, ‘Future’, ‘Expired/Inactive’)”
1 Like
Can you refer this
“Viability =
VAR StatusViable =
IF (
AND (
pqAgreements[Agreement - Status] <> “40”,
pqAgreements[Agreement Line - Status] <> “20”
),
FALSE,
TRUE
)
VAR CurrentViable =
IF (
AND (
AND (
TODAY () <= pqAgreements[Agreement Line - Valid To],
TODAY () >= pqAgreements[Agreement Line - Valid From]
),
AND (
TODAY () <= pqAgreements[Agreement - Valid To],
TODAY () >= pqAgreements[Agreement - Valid From]
)
),
TRUE,
FALSE
)
VAR FutureViable =
IF (
AND (
AND (
TODAY () <= pqAgreements[Agreement Line - Valid To],
TODAY () <= pqAgreements[Agreement Line - Valid From]
),
AND (
TODAY () <= pqAgreements[Agreement - Valid To],
TODAY () <= pqAgreements[Agreement - Valid From]
)
),
TRUE,
FALSE
)
RETURN
IF (
AND ( StatusViable, CurrentViable ),
“Current”,
IF ( AND ( StatusViable, FutureViable ), “Future”, “Expired/Inactive” )
)”
Is it one calculated field?
Same here.
- ifelse syntax
- today() = now()
ifelse( AND ( {agreement - status} <> ‘40’,
{agreement line - status} <> ‘20’
), AND ( AND (now() <= {agreement line - valid to},now() >= {agreement line - valid from}
), AND (now() <= {agreement line - valid to}, now() >= {agreement - valid from})),
(AND (AND (now() <= {agreement line - valid to}, now() <= {agreement line - valid from}),
AND ( now() <= {agreement - valid to}, now() <= {agreement - valid from})), ‘Current’, ‘Future’, ‘Expired/Inactive’)
This won’t work too
Hi @Hemant_rangdal
the AND part doenst look right.
Should be "condition" and "condition"
1 Like
We hope the response from @ErikG helped you to solve the calculated field expression .Am marking questions as solved , please let us know if you still need assistance.
1 Like