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

 

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

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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