Weekly Updates - Net New + Existing Records

Power Query Data Setup
1

Most of the examples from the courses I’ve watched utilize a dataset where each record represents a “unique” transaction.

I have an issue where I’m charting pipeline progression (movement of records through sales stages) and need to demonstrate weekly progression of existing, or net-new, records.

Therefore, my weekly import “may” contain records that are new (timestamped this week), or existing where some key metric I’m tracking may have changed (could be 1 or more columns of data that have changed for an existing record).

I’ve come up with an idea of creating an update process where I “signal” via a new column, the last know occurence of a record:

PSEUDO CODE:
Latest_Record =
VAR CurrentOPID = CRM_Data[OPID]
VAR CurrentDate = CRM_Data[IMPRTDATE]
RETURN
IF(
COUNTROWS(
FILTER(
CRM_Data,
CRM_Data[OPID] = CurrentOPID &&
CRM_Data[IMPRTDATE] > CurrentDate
)
) = 0,
1,
0
)

QUESTION: Is my logic sound enough to handle various measures like the ones below?

If I have this data, for example:
IMPRTDATE; OPDATE; OPID; GROSS_PROFIT; SALES_STAGE
05/01/2024; 05/01/2024; 12345; $50,000; Working
05/08/2024; 05/01/2024; 12345; $50,000; Working
05/15/2024; 05/01/2024; 12345; $50,000; Qualifying
05/23/2024; 05/01/2024; 12345; $48,000; Qualifying
05/30/2024; 05/01/2024; 12345; $48,000; Qualifying
06/05/2024; 05/01/2024; 12345; $42,000; Decision Due

A) The SUM of Gross profit for this account, in May, should be $48,000;
B) The SUM of Gross Profit for the account, in June, should be $42,000;
C) The cumulative SUM of Gross Profit for this account, to date, should be $42,000
D) The cumulate count of records for this account, to date, should be 1
E) The net-new count of this account, in May, should be 1 (OPDATE==May)
F) The net-new count of this account, in June, should 0 (OPDATE==May)
G) This-Month versus Last Month count should be 0 for June and 1 for May

Any help / advise is tremendougly appreciated.

2

Hi @mtyler306 ,

Your logic for handling the latest record based on the import date is sound. Here’s how you can achieve your goals using DAX in Power BI:

Create the Latest Record Flag:

DAX
Copy code
Latest_Record =
VAR CurrentOPID = CRM_Data[OPID]
VAR CurrentDate = CRM_Data[IMPRTDATE]
RETURN
IF(
COUNTROWS(
FILTER(
CRM_Data,
CRM_Data[OPID] = CurrentOPID &&
CRM_Data[IMPRTDATE] > CurrentDate
)
) = 0,
1,
0
)
SUM of Gross Profit for May:

DAX
Copy code
SUM_May_GP =
CALCULATE(
SUM(CRM_Data[GROSS_PROFIT]),
FILTER(
CRM_Data,
CRM_Data[IMPRTDATE] <= DATE(2024, 5, 31) && CRM_Data[IMPRTDATE] >= DATE(2024, 5, 1) &&
CRM_Data[Latest_Record] = 1
)
)
SUM of Gross Profit for June:

DAX
Copy code
SUM_June_GP =
CALCULATE(
SUM(CRM_Data[GROSS_PROFIT]),
FILTER(
CRM_Data,
CRM_Data[IMPRTDATE] <= DATE(2024, 6, 30) && CRM_Data[IMPRTDATE] >= DATE(2024, 6, 1) &&
CRM_Data[Latest_Record] = 1
)
)
Cumulative SUM of Gross Profit to Date:

DAX
Copy code
Cumulative_GP =
CALCULATE(
SUM(CRM_Data[GROSS_PROFIT]),
FILTER(
CRM_Data,
CRM_Data[Latest_Record] = 1
)
)
Cumulative Count of Records to Date:

DAX
Copy code
Cumulative_Count =
CALCULATE(
COUNTROWS(CRM_Data),
FILTER(
CRM_Data,
CRM_Data[Latest_Record] = 1
)
)
Net-New Count in May:

DAX
Copy code
NetNew_May =
CALCULATE(
COUNTROWS(CRM_Data),
FILTER(
CRM_Data,
MONTH(CRM_Data[OPDATE]) = 5 &&
CRM_Data[Latest_Record] = 1
)
)
Net-New Count in June:

DAX
Copy code
NetNew_June =
CALCULATE(
COUNTROWS(CRM_Data),
FILTER(
CRM_Data,
MONTH(CRM_Data[OPDATE]) = 6 &&
CRM_Data[Latest_Record] = 1
)
)
This-Month vs Last-Month Count:

DAX
Copy code
ThisMonth_vs_LastMonth =
VAR ThisMonth =
CALCULATE(
COUNTROWS(CRM_Data),
FILTER(
CRM_Data,
MONTH(CRM_Data[IMPRTDATE]) = MONTH(TODAY()) &&
CRM_Data[Latest_Record] = 1
)
)
VAR LastMonth =
CALCULATE(
COUNTROWS(CRM_Data),
FILTER(
CRM_Data,
MONTH(CRM_Data[IMPRTDATE]) = MONTH(TODAY()) - 1 &&
CRM_Data[Latest_Record] = 1
)
)
RETURN
ThisMonth - LastMonth

For more detailed guidance and advanced Power BI queries, you can explore the Data Mentor platform.

Cheers,

Enterprise DNA Support Team