Compare Row to Same Column While Filtering on Date

[Anonymous]

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 report to track the changes. I provided a simplified version of the data below. The Change column for 4/22/20 can be blank because it is the starting point thus no prior report to compare.

• if Claim # does not match any of the prior report date Claim # then return "New" (Example: Row 7, 12, 13)
• if Claim # match the previous report date Claim # AND:
  • Status = prior Status AND Last Saved Date < prior Report Date then return "No Change" (Example: Row 6, 8 )
  • Status = prior Status AND Last Saved Date >= prior Report Date then return "Updated" (Example: Row 9, 11)
  • Else return current report date's status (Example: Row 5, 10)

[v-lili6-msft · Accepted solution]

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

[Anonymous]

Thank you! I made some minor edits and it works as I intended.

[Greg_Deckler]

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...