Forum Discussion
PowerBI123456
5 years agoPost Partisan
Comparing Dates from 2 tables
Hi, I have 2 tables (notes & payments) that have 2 different dates (note date and payment date) and have a relationship between the account numbers in the 2 tables. How can I create a measure to ...
- 5 years ago
Hi PowerBI123456 ,
If your notes date table has single notes date for each account number, you can create a measure like this to count:
Count = VAR tab = FILTER ( ALL ( Payment ), 'Payment'[Payment date] > CALCULATE ( MIN ( 'Notes'[Note date] ), 'Notes'[Account number] IN DISTINCT ( 'Payment'[Account number] ) ) ) VAR tb = SUMMARIZE ( ADDCOLUMNS ( tab, "Count", COUNTX ( FILTER ( tab, [Account number] = EARLIER ( Payment[Account number] ) ), [Payment date] ) ), [Account number], [Count] ) RETURN COUNTX ( FILTER ( tb, [Count] > 1 ), [Account number] )Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yingjl
5 years agoCommunity Support
Hi PowerBI123456 ,
If your notes date table has single notes date for each account number, you can create a measure like this to count:
Count =
VAR tab =
FILTER (
ALL ( Payment ),
'Payment'[Payment date]
> CALCULATE (
MIN ( 'Notes'[Note date] ),
'Notes'[Account number] IN DISTINCT ( 'Payment'[Account number] )
)
)
VAR tb =
SUMMARIZE (
ADDCOLUMNS (
tab,
"Count",
COUNTX (
FILTER ( tab, [Account number] = EARLIER ( Payment[Account number] ) ),
[Payment date]
)
),
[Account number],
[Count]
)
RETURN
COUNTX ( FILTER ( tb, [Count] > 1 ), [Account number] )
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.