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,
