How can I find added/removed/changed records from an updated query based on a Report Date column?

[Mrloler2]

Hello!

I have an Excel data sheet that is set up on a monthly refresh schedule and pulls in aggregate project information, appending it to the previous data set which is then set apart by a Report Date column. This is all in a single table/single sheet feeding into Power BI. For instance:

Project ID	Stage	Report Date
PRJA	Concept	4/27/2024
PRJB	Plan	4/27/2024
PRJC	Execute	4/27/2024
PRJD	Close	4/27/2024
PRJA	Concept	5/27/2024
PRJB	Execute	5/27/2024
PRJC	Execute	5/27/2024
PRJE	Concept	5/27/2024

I need to create a new table or measure to be able to single-out changes between report dates which are selectable by the user. For instance, this table will grow - so while right now I'll only need to reference changes from April to May, I'll soon need to reference changes from May to June, etc.

In this example, and in reference to just these two dates above, I would need to be able to call out that:

• PRJB had a Stage Change from Plan to Execute between the selected dates
• PRJD was removed between the two dates
• PRJE was added between the two dates

If to draw this information out I need to create multiple measures or multiple tables, that's fine - I just need to be able to weed out the changes against current and future report dates.

Thank you very much in advance! I hope I've explained this well.

Thanks,

Jerry

[danextian]

Please try the approach in the attached pbix.

[KarWar]

Thank You!

[danextian]

Hi @Mrloler2 

Questions:

• Will the user be selecting two consecutive months or are they allowed to skip months? Example: April and June.
• Can a  project once closed be reopened?
• Can report dates for the same project be within the same month?
• When you say removed between the two dates, does the status change or the record gets deleted?

If it just  a simple comparison of the statuses between the current and previous report date, you can try the calculated column below. Please note that PRJD is tagged as added as it doesn't have a prior record.

[Mrloler2]

@danextian ,

Thank you for the prompt reply. Answers to your questions:

• Will the user be selecting two consecutive months or are they allowed to skip months? Example: April and June. To make it easier, users really don't need to select any months as this will always (and only) be a reflection of the current and prior month (i.e., what changed from last month/report to the current month/report). So the formula should only look at the current and prior report date (max and max -1?).
• Can a  project once closed be reopened? Though unlikely, it's possible and having this check in place would allow us to monitor this circumstance.
• Can report dates for the same project be within the same month? This is possible as we consider a move to bi-weekly reporting instead of monthly, so having the ability to reference the most recent two report dates would be required (whether month-to-month or intra-month (referencing my response to the first bullet above).
• When you say removed between the two dates, does the status change or the record gets deleted? A project could potentially be cancelled and this check would allow us to check for missing entities report-over-report as it wouldn't feed into our master file.

I hope this helps clarify my situation. I essentially need to be able to visualize an answer to the following business questions:

1. "What projects had a change in STAGE this report compared to last report - and what is that new stage?"
2. "What projects are new to the mix that weren't on the last report?"
3. "What projects are no longer on the report, that were on the last report?"

Helping uncover the formula for this will help me answer a number of other time-based analysis questions, but these are the most critical. I'm racking my brain over how to showcase this and just keep hitting dead ends. Not sure if I need to look at a Conditional Column, New Table or New Query to get this detail.

Thanks for your help!

Jerry