Changes within same Fact Table

DAX
1

Hi all,
I have an interesting case. We have 1 master fact table that has several month reports appended on top of report. There are 2 date fields, report date and sales date. We would like to have a measure that dynamically identifies new transactions and no longer existing transactions. For example, first logic for new transactions, the transaction exists on the May 2022 report, but not on the April 2022 report, therefore it is “New”. The opposite for old, the transaction existed in April 2022, but not in May 2022, therefore the outstanding item has been received.

Ultimately, the ability to be dynamic via a date slice would be ideal. If the user could select the June report and know what was “New” and what was “Received” best on a measure.

I can provide a mockup PBIX file, but wanted to reach out to see if the above was simple enough to garner a response.

2

Bumping this post for more visibility from our experts and users.

3

@CalebAFord I think you can use the New and Lost Customer pattern from https://www.daxpatterns.com/new-and-returning-customers/

4

Hello @CalebAFord

Did the response from @AntrikshSharma help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

5

This pattern is exactly what we needed, thank you!