calculate value between two dates

[dkc]

Hi, 

I have two tables: 

Promo list: with promo ID, customers and contact period (contact start and end date). 

Contact list: Customer, contact date and nr. of contacts

I want to calculate a measure which says: for promo ID XYnumber of contacts have been conducted considering the defined contact periods and participating customers of an Promo ID. I already tried to work with datesbetween formula but was not successful.

Could you pls help me to create the correct measure?

Results expected: 

Promo ID1: 1 contact

Promo ID2: 0 contacts

Promo ID3: 0 contacts

Thank you in advance for your support!

[dkc]

thank you!

[v-easonf-msft · Accepted solution]

Hi, @dkc 

Please try formula as below:

Max Contact Date= 
CALCULATE (
    MAX ( 'Contact list'[Contact Date] ),
    FILTER (
        'Contact list',
        'Contact list'[Customer] = MAX ( 'Promo list'[Customer] )
            && 'Contact list'[Contact Date] >= MAX ( 'Promo list'[Contact Start date] )
            && 'Contact list'[Contact Date] <= MIN ( 'Promo list'[Contact End date] )
    )
)

Count = 
CALCULATE (
    DISTINCTCOUNT ( 'Promo list'[Customer] ),
    FILTER ( 'Promo list', [Max  Contact Date] <> BLANK () )
) + 0

Best Regards,
Community Support Team _ Eason