Forum Discussion

dkc's avatar
dkc
Helper I
4 years ago
Solved

calculate value between two dates

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!

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    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