Counting records based on two dates, second count must exclude records from the first

[jbobeck]

Hi Folks,

I have two tables, a date dimension and a fact.  The fact table has two date columns, the date a record was added and a date if/when it was edited.  What I need to accomplish is generating a count for the number of records added and number of records edited in a date range.  I can accomplish this with two relationships between the two tables and using USERELATIONSHIP() in the count.  The trick is that if a record has both the add and edit date in the range, it should only be included in the add count. This is where it falls apart on me; I can't determine how I can get the edit count to exclude records in the add count.

Any advice is welcome, TIA.

[govind_021]

Hey , use this Approach

Edited Count =
CALCULATE(
COUNTROWS(FactTable),
USERELATIONSHIP(DateTable[Date], FactTable[EditedDate]),
NOT(
CONTAINS(
VALUES(FactTable[RecordID]),
FactTable[RecordID],
CALCULATETABLE(
VALUES(FactTable[RecordID]),
USERELATIONSHIP(DateTable[Date], FactTable[AddedDate])
)
)
)
)

[jbobeck · Accepted solution]

I was able to use this as a basis and got it working:

Edited Count =
CALCULATE(
COUNTROWS(FactTable),
USERELATIONSHIP(DateTable[Date], FactTable[EditedDate]),
NOT(FactTable[RecordID] IN VALUES(FactTable[RecordID]))
)

I'm not sure how this is giving me the correct answer, but it must recognize the context that the two FactTable[RecordID]s are from different sets.

[jbobeck]

Thanks for the reply!

This is giving an error: A function "CONTAINS" has been used in a True/False expressions that is used as a table filter expression.

CONTAINS and CALCULATETABLE are new to me, I'll do some exploration with them and see if I can get the error corrected.

[lbendlin]

You can use EXCEPT for that. Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[jbobeck]

I'll take a look at how to use EXCEPT.

The data is very simple, I have a date dimension with sequential dates from 1950 - 2050.  The fact table is only 4 columns and is laid out like below:

FactID	AddDT	EditDT	RecordValue
1	1/1/2024	6/15/2024	ABC123
2	2/16/2024		DEF456
3	2/22/2024		GHI789
4	4/19/2024	6/16/2024	JKL012
5	6/30/2024	8/1/2024	MNO345
6	7/2/2024		PQR678
7	8/9/2024	10/31/2024	STU901
8	10/31/2024		VWX234
9	11/1/2024		YZZ567
10	12/10/2024		AAA890