Compare the values of two different dates (single date column) in the same table for each category

[IndrachapaLA]

Hi All,

I'm trying to return a Yes / No value in a card visual in order to trigger a notificatoin alert to team members based on below condition.
IF(lates coverage % < previous coverage % for at least one project, "yes", "No")

If this condition is true for at least one project, I need to alert the particular team.

I use weekly data.

I tried below dax and it didn't work.

Alert1 =

var _latestCoverageValue = CALCULATE(MAX('Table1'[Coverage Value]),'Table1'[Date]=MAX('Table1'[Date]))

var _previousCoverageValue = CALCULATE(MAX('Table1'[Coverage Value]),'Table1'[Date]=(MAX('Table1'[Date])-7))

var _result =

IF(

    SELECTEDVALUE('Table1'[projectName])&&(_latestCoverageValue<_previousCoverageValue),

    "Yes", "NO"

)

return

    _result

Can anyone help with this please.
thanks

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but please try something like below.

Alert1 =
VAR _project =
    ADDCOLUMNS (
        DISTINCT ( 'Table1'[projectName] ),
        "@latest",
            CALCULATE (
                MAX ( 'Table1'[Coverage Value] ),
                'Table1'[Date] = MAX ( 'Table1'[Date] )
            ),
        "@previous",
            CALCULATE (
                MAX ( 'Table1'[Coverage Value] ),
                'Table1'[Date]
                    = ( MAX ( 'Table1'[Date] ) - 7 )
            )
    )
VAR _condition =
    COUNTROWS ( FILTER ( _project, [@latest] < [@previous] ) )
VAR _result =
    IF ( _condition >= 1, "Yes", "NO" )
RETURN
    _result

[IndrachapaLA]

Hi, Thank you so much for the support.