Identifying the count of changes in a comparison measure between two or more snap shot date files

[gemcityzach]

From this code that @lbendlin helped me put together, I am able to identify which records change between two date files, is it possible to identify the count of changes that occur in a period? Is it possible to get this measure to work correctly in a Table vis instead of a Matrix? For some reason Table doesn't work but Matrix works fine.

dailyDeltas = 
--measure to identify New Records, No Changes or What Changes within Two specified fields between all files dates in a folder
--instantiate 'mxd', which is the largest file date name in the folder

var mxd = max('rep-daily'[snapshot_dt])

--instantiate 'pd', which is the previous date (largest, less-than file of the mxd, i.e., yesterday is smaller than today)
var pd = CALCULATE(max('rep-daily'[snapshot_dt]),'rep-daily'[snapshot_dt]<mxd)

--instantiate 'pv', a calculated table removing all filters except 'ID' and snapshot date from 'pd'
var pv = CALCULATETABLE('rep-daily',ALLEXCEPT('rep-daily','rep-daily'[ID]),'rep-daily'[snapshot_dt]=pd)

--return the results of the checks for new records, no changes or changed values in target fields
return if (ISBLANK(pd),"New Record", --return "New Record" if 'pd' ISBLANK = TRUE, which is TRUE when pd is NOT today (mxd)

-- setup to identify changes in the target fields by determing if a record in a previous value has changed
-- instantiate 'chg' 
var chg = if(maxx(pv,[Overall Control Effectiveness])<>max('rep-daily'[Overall Control Effectiveness]),"Overall Control Effectiveness: " & maxx(pv,[Overall Control Effectiveness]) & " => " & max('rep-daily'[Overall Control Effectiveness])) 
        & if(maxx(pv,[Overall Inherent Risk])<>max('rep-daily'[Overall Inherent Risk])," Overall Inherent Risk: " & maxx(pv,[Overall Inherent Risk]) & " => " & max('rep-daily'[Overall Inherent Risk]))
return if(len(chg)>0,chg,"No Change")) -- return the results if the length of 'chg' > 0 then "No Change"

For example, if I give you three days of 'snap shot' records, is it possible to identify the count of changed records from one day to another?

ID	Overall Inherent Risk	Overall Control Effectiveness	snapshot_dt
1809498	Moderate	Satisfactory	3/1/2024
1809499	Low	Satisfactory	3/1/2024
1809500	Low	Satisfactory	3/1/2024
1809501	Moderate	Satisfactory	3/1/2024
1809502	Moderate	Satisfactory	3/1/2024
1809503	High	Satisfactory	3/1/2024

ID	Overall Inherent Risk	Overall Control Effectiveness	snapshot_dt
1809498	High	Satisfactory	3/2/2024
1809499	Low	Satisfactory	3/2/2024
1809500	Low	Unsatisfactory	3/2/2024
1809501	Low	Satisfactory	3/2/2024
1809502	Moderate	Satisfactory	3/2/2024

ID	Overall Inherent Risk	Overall Control Effectiveness	snapshot_dt
1809498	Low	Satisfactory	3/3/2024
1809499	Low	Satisfactory	3/3/2024
1809500	High	Satisfactory	3/3/2024
1809501	Low	Satisfactory	3/3/2024
1809502	Moderate	Satisfactory	3/3/2024
1809503	High	Satisfactory	3/3/2024

[gemcityzach]

(facepalm) I was using a measure and not a column. I put it into a column and it works perfectly! It also detects when a record on say 3-2 changes and then that same record on 3-5 changes back to the original. It's nearly perfect! Do you know if it's possible to identify 'New Record' and/or what value (inherent risk or control effectiveness) changed in a given change record? Or if a record is missing a value?

Edit: I did find some instances where it didn't work. If I didn't have the very next daily file (e.g., 4-1 and then 4-3), it did not detect a change in the 4-3 file.

[ryan_mayu]

What about merge the data and create a column

Column =

var _last=maxx(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[snapshot_dt]=EARLIER('Table'[snapshot_dt])-1),'Table'[Overall Inherent Risk])

VAR _last2=maxx(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[snapshot_dt]=EARLIER('Table'[snapshot_dt])-1),'Table'[Overall Control Effectiveness])

return if(ISBLANK(_last),BLANK(),if('Table'[Overall Control Effectiveness]<>_last2||'Table'[Overall Inherent Risk]<>_last,"changed"))

pls see the attachment below

[gemcityzach]

I'm not sure but someone marked this as a solution. Maybe it worked for them but it didn't work for me. I'm getting an error on Earlier: "Earlier/Earliest refers to an earlier row context which doesn't exist". I'm not quite sure why.

In _var _last, "EARLIER" block I'm getting an error that says [ID] doesn't exist. Same for snapshot_dt. 

In var _last2 I'm getting an error that says snapshot_dt doesn't exist.

In return I'm getting an error for overall control effectiveness and overall inherent risk that says neither of them exist.

My code:

col2 =

var _last=maxx(

    FILTER(

        'tbl-daily','tbl-daily'[P-P-R ID] = EARLIER (

        'tbl-daily'[P-P-R ID])

        && 'tbl-daily'[snapshot_dt] = EARLIER([snapshot_dt])-1

    ),'tbl-daily'[Overall Inherent Risk]

)--end _last

var _last2=maxx(

    FILTER(

        'tbl-daily','tbl-daily'[snapshot_dt]=EARLIER('tbl-daily'[snapshot_dt]) && 'tbl-daily'[snapshot_dt]=EARLIER('tbl-daily'[snapshot_dt])-1),'tbl-daily'[Overall Control Effectiveness])

return if(ISBLANK(_last),BLANK(),if('tbl-daily'[Overall Control Effectiveness] <> _last2 ||'tbl-daily'[Overall Inherent Risk]<>_last,"changed"))

[ryan_mayu]

is the data in the attachment the smililar layout of your real data? 

are you creating a column  or a measure? I think you need to create column