Calculating Changes in Cumulative/Appended Report

[Twizxup]

Hi All,

I receive a report every week that consists of our company's current cost centers and, each week, I append it to the previous week's report. My ultimate goal is to only return a list of changes that have taken place (then/now). Below is a sample dataset of the appended reports (my source data).

Report Date	Cost Center Code	Cost Center Name	Cost Center Division	Cost Center Department	Cost Center Owner
1/1/2025	123	Financial Planning1	Finance	Financial Planning	Janet
1/1/2025	234	Compliance1	Human Resources	Compliance	Ed
1/1/2025	345	Customer Sales Analysis1	Sales	Customer Sales Analysis	Madison
1/1/2025	456	Data Analytics1	Chief Operating Office	Data Analytics	Mitch
1/1/2025	567	Corporate Finance1	Finance	Corporate Finance	Chris
1/8/2025	123	Financial Planning1	Finance	Financial Planning	Janet
1/8/2025	234	Compliance1	Human Resources	Compliance	Ed
1/8/2025	345	Customer Sales Analysis2	Sales	Customer Sales Analysis	Madison
1/8/2025	456	Data Analytics1	Chief Operating Office	Data Analytics	Luka
1/15/2025	123	Financial Planning1	Finance	Financial Planning	Janet
1/15/2025	234	Compliance1	Human Resources	Compliance	Ed
1/15/2025	345	Customer Sales Analysis2	Sales	Customer Sales Analysis	Madison
1/15/2025	456	Data Analytics1	Chief Operating Office	Data Analytics	Luka
1/15/2025	678	Taxes1	Finance	Taxes	Adam

Using this data, I'd like to return a table that captures only the week-to-week changes, including cost centers that have been added or removed. An example of the desired output is below.. please note that the green font represents the values that changed, and the "Explanation" field with orange font is just a helper field to explain what changed (although I also want to create this calculated field, which I believe should be pretty easy).

Cost Center Code	Then/Now	Report Date	Cost Center Name	Cost Center Division	Cost Center Department	Cost Center Owner	Explanation
345	Then	1/1/2025	Customer Sales Analysis1	Sales	Customer Sales Analysis	Madison	Cost Center Name Change
345	Now	1/8/2025	Customer Sales Analysis2	Sales	Customer Sales Analysis	Madison	Cost Center Name Change
456	Then	1/1/2025	Data Analytics1	Chief Operating Office	Data Analytics	Mitch	Cost Center Owner Change
456	Now	1/8/2025	Data Analytics1	Chief Operating Office	Data Analytics	Luka	Cost Center Owner Change
567	Then	1/1/2025	Corporate Finance1	Finance	Corporate Finance	Chris	Cost Center Removed
567	Now	1/8/2025					Cost Center Removed
678	Then	1/8/2025					Cost Center Added
678	Now	1/15/2025	Taxes1	Finance	Taxes	Adam	Cost Center Added

Any help would be hugely appreciated! Thank you in advance!

[v-shamiliv]

Hi @Twizxup 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-shamiliv]

Hi @Twizxup 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-shamiliv]

Hi @Twizxup 
Thank you for reaching out microsoft fabric community forum.

you're right that the current logic only captures the first change, which isn't ideal when multiple fields can change at once.

The good news is, you don’t need to hardcode every combination. Instead, you can create a dynamic DAX expression that checks each field for changes and then combines any differences into a single "Change Type" field like:

“Cost Center Name Change + Cost Center Owner Change”

This way, it picks up all changes automatically, no matter how many fields you’re tracking. And if you prefer to list each change on a separate row (one per field), that’s also possible.


If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.

[rajendraongole1]

Hi @Twizxup  - create a calculated column in your table to get the previous report date for each Cost Center.

Previous Report Date =
VAR CurrentDate = 'CostCenters'[Report Date]
VAR CostCenter = 'CostCenters'[Cost Center Code]
RETURN
MAXX(
FILTER(
'CostCenters',
'CostCenters'[Cost Center Code] = CostCenter &&
'CostCenters'[Report Date] < CurrentDate
),
'CostCenters'[Report Date]
)

let's create a calculated table to identify changes between weeks

ChangesTable =
VAR CurrentData = 'CostCenters'
VAR PreviousData =
ADDCOLUMNS(
CurrentData,
"Previous Report Date",
[Previous Report Date],
"Previous Cost Center Name",
LOOKUPVALUE('CostCenters'[Cost Center Name], 'CostCenters'[Cost Center Code], 'CostCenters'[Cost Center Code], 'CostCenters'[Report Date], [Previous Report Date]),
"Previous Cost Center Owner",
LOOKUPVALUE('CostCenters'[Cost Center Owner], 'CostCenters'[Cost Center Code], 'CostCenters'[Cost Center Code], 'CostCenters'[Report Date], [Previous Report Date])
)

RETURN
FILTER(
ADDCOLUMNS(
PreviousData,
"Change Type",
SWITCH(
TRUE(),
ISBLANK([Previous Report Date]), "Cost Center Added",
ISBLANK([Cost Center Name]) && ISBLANK([Cost Center Owner]), "Cost Center Removed",
[Previous Cost Center Name] <> [Cost Center Name], "Cost Center Name Change",
[Previous Cost Center Owner] <> [Cost Center Owner], "Cost Center Owner Change",
BLANK()
)
),
NOT(ISBLANK([Change Type]))
)

Hope now ,you can then display this table in Power BI and apply conditional formatting to highlight changes.

[Twizxup]

This is great and hugely helpful! The only problem is (and I should have included this scenario in the example):

When there are multiple changes to the same cost center simultaneously, only the first change in the logic is captured. For example, if both the Cost Center Name and Cost Center Owner changes for cost center 345, the Change Type field only captures "Cost Center Name Change" as that is the first item in the logic..

Is there any way to append these values automatically so that the Change Type field says something like "Cost Center Name Change + Cost Center Owner Change"

I understand I could manually code this like:

[Previous Cost Center Name] <> [Cost Center Name] && [Previous Cost Center Owner] <> [Cost Center Owner], "Cost Center Name Change + Cost Center Owner Change"

The problem with this, though, is that the data I provided is only a sample, and the actual number of fields I need to track changes for is 9. So, that would require about 400 million possible combinations (9^9) which is obviously infeasible.

Thank you again for getting me this far, and if you have any advice on this, I would reaaaally appreciate it! 

[Twizxup]

@rajendraongole1 Just following up on this to see if you know of a solution/workaround to handle multiple changes (a cost center could experience one change (ex: Cost Center Name), or it could experience multiple changes simultaneously (ex: Cost Center Name, Cost Center Division, & Cost Center Owner))..

Correction to my earlier statement: With 9 fields subject to change, I believe there are ~363,000 different combinations (excluding repetitions) which, still, makes it unrealistic to code.

Instead, is there a way to append the 'Change' values as I mentioned before or, if it would be easier, would it be possible to have the code continue to execute after a change is identified, and put each change on a separate line? For example:

Cost Center Code	Change Type
345	Cost Center Name Change
345	Cost Center Owner Change
345	Cost Center Division Change

Thanks again!