Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Compare Row to Same Column While Filtering on Date

Hello, I am trying to create a calculated column to track day-to-day changes to claims. The highlighted Change column is the objective. I want to compare the Claim # to only the prior working day rep...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Anonymous 

    You could use this logic to get it:

    Change = 
var _firstdate=CALCULATE(MIN('Table'[Report Date]),ALL('Table'))
var _perdate=CALCULATE(MAX('Table'[Report Date]),FILTER('Table','Table'[Report Date]<EARLIER('Table'[Report Date]))) 
var _perclaimstatus=CALCULATE(MAX('Table'[Status]),FILTER('Table','Table'[Report Date]=_perdate&&'Table'[Claim #]=EARLIER('Table'[Claim #])))
return
IF (
        _firstdate = 'Table'[Report Date],
        BLANK (),
        IF (
            CALCULATE (
                MAX ( 'Table'[Claim #] ),
                FILTER (
                    'Table',
                    'Table'[Report Date] = _perdate
                        && 'Table'[Claim #] = EARLIER ( 'Table'[Claim #] )
                )
            )
                = BLANK (),
            "New",
            IF (
                'Table'[Last Saved Date] = BLANK ()
                    && _perclaimstatus <> 'Table'[Status],
                'Table'[Status],
                IF (
                    _perclaimstatus = 'Table'[Status]
                        && 'Table'[Last Saved Date] < _perdate,
                    "No Change",
                    IF (
                        _perclaimstatus = 'Table'[Status]
                            && 'Table'[Last Saved Date] >= _perdate,
                        "Updated"
                    )
                )
            )
        )
    )

    Result:

     

    and here is sample pbix file, please try it.

     

    Regards,

    Lin