Forum Discussion

jbobeck's avatar
jbobeck
Frequent Visitor
1 year ago
Solved

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

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 generat...
  • jbobeck's avatar
    jbobeck
    1 year ago

    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.

govind_021's avatar
govind_021
Super User
1 year ago

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's avatar
jbobeck
Frequent Visitor
1 year ago

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.