Flagging records that exist in 2 tables based on close date!

Jitmondo — Wed, 01 Feb 2023 17:53:29 GMT

Hi All,

Scenario

I have 2 tables, where one tells me the expected sales return at the beginning of the month

and one where the actuals sales numbers are stored.

I want to see how much of the expected sales were actually made, this means I have to only sum up sales from the actual table that existed in the snapshot table on the 1sst of the month. I have correctly calculated the expected amount from the snapshot table.

The next step was to create a flag where I used the Lookup function to flag as 1 or 0 if the id from actuals was present in the snapshot table so I can filter on the records when I use a measure.

Desired Outcome

if I have 3 Closed Won sales with an asofdate = 01/01/2023 and a historical date within January 23 I would expect to see those three sales only in my actual measure.

Problem

The above flag can only use id so it will not help me identify the sales in the period. Instead of getting 3 sales like the example above I get other sales too which exist in both tables.

Is there a way to flag sales that were expected to closed in month to my actuals table so I only see sales completed that were expected and filter out sales opened and closed in month.

Table 1 has Information for sales as of the first day of the month and looks like the below

ID	AS OF DATE	HISTORICAL CLOSE DATE
125	01/01/2023	01/01/2023
516	02/01/2023	02/01/2023
514	03/01/2023	06/12/2022
153	04/01/2023	04/01/2023
235	05/01/2023	05/01/2023
89	06/01/2023	09/02/2023
894	07/01/2023	10/02/2023
545	08/01/2023	11/02/2023
147	09/01/2023	04/12/2022
754	10/01/2023	05/12/2022
458	11/01/2023	06/12/2022

Table 2 is a standard actuals table where I have put in a flag using a lookup function. this is the table where I will use a measure for a visual.

I have tried to use power query to help but due to the model build it will not allow me to group/merge and I am currently trying to use a virtual table which is not quite there yet.

If you kept reading thanks ! hopefully I havent confused you 🙂

if you have any ideas on what I could do please let me know,

topic Flagging records that exist in 2 tables based on close date! in DAX Commands and Tips

Flagging records that exist in 2 tables based on close date!