Check History (new or deleted records)

[Sachy123]

so I have the following data set                                                      (Happy new Year to everyone BTW 😊)

ReportDate	Record ID	UpdateDate	Column A	Column B
31st December 2020	1	20/12/2020 8:00:00	10	80
31st December 2020	2	15/12/2020 10:30:00	88	00
31st December 2020	4	01/12/2020 8:00:00	44	32
30th November 2020	1	29/11/2020 10:30:00	10	80
30th November 2020	2	26/11/2020 14:00:33	44	67
30th November 2020	3	15/11/2020 15:00:44	50	20

I am planning to make a filter on Report date and show or highlight the records which have changed / updated or deleted.

Do we have a comparison like that? 

Like e.g.

if I select Report Date 31st December 2020

Then I should see all the records like as below

ReportDate	Record ID	UpdateDate	Column A	Column B
31st December 2020	1	20/12/2020 8:00:00	10	80
31st December 2020	2	15/12/2020 10:30:00	88	00
31st December 2020	4	01/12/2020 8:00:00	44	32

Green = No Change

Red = Update

Blue = New

[v-stephen-msft]

Hi @Sachy123 ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Stephen Tao

[v-stephen-msft · Accepted solution]

Hi @Sachy123 ,

The pbix file I tested is here.

1.Create the measure.

Measure =
VAR _date =
    SELECTEDVALUE ( 'Table'[ReportDate] )
VAR _pre =
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[ReportDate] < _date ), [ReportDate] )
VAR _a =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Record ID] = MAX ( 'Table'[Record ID] )
                && 'Table'[ReportDate] = _pre
        ),
        [Column A]
    )
VAR _b =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Record ID] = MAX ( 'Table'[Record ID] )
                && 'Table'[ReportDate] = _pre
        ),
        [Column B]
    )
RETURN
    IF (
        MAX ( 'Table'[Column A] ) = _a
            && MAX ( 'Table'[Column B] ) = _b,
        "Green",
        IF (
            _a = BLANK ()
                && _b = BLANK (),
            "Blue",
            IF ( MAX ( 'Table'[Column A] ) <> _a || MAX ( 'Table'[Column B] ) <> _b, "Red" )
        )
    )

2.In the visual, set the font color as follows.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Sachy123 , Create a nea column like this

Color column =
var _max = filter(Table, [Record ID] =earlier([Record ID]) && [UpdateDate] <earlier([UpdateDate])),[UpdateDate])
var _valA = filter(Table, [Record ID] =earlier([Record ID]) && [UpdateDate] =_max),[Column A])
var _valB = filter(Table, [Record ID] =earlier([Record ID]) && [UpdateDate] =_max),[Column B])
return
Switch( True(),
isblank(_valA) , "New",
[Column A] =_valA && [Column B] = _valB , "No Change",
"Update"
)

Use this in conditional formatting for each column using "Field value" option use First or Last for Aggregation

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[Sachy123]

Hello @amitchandak 

Thanks, but I cannot use EARLIER function, 

it says "Parameter is not correct type" Cannot find the name "Record ID"

I have also linked the table to a time dimension.