Create new column from filtered data of another table

[Anonymous]

Hello all,

I tryed to find any similar topics but without success 😞

I want table with values from metrics table filtered only for live customers – customers with least one valid validity in that date.

Metrics table

Stores measured metrics from customers in monthly period (last day of month)

Sample date	CustomerId	Metric1	Metric2
2020-12-31	1	100	200
2020-12-31	2	50	50
2020-12-31	3	20	10
2020-12-31	4	1	1
2021-01-31	1	110	210
2021-01-31	2	55	60
2021-01-31	3	25	15
2021-01-31	4	5	5
2021-02-28	1	115	230
2021-02-28	2	55	60
2021-02-28	3	30	25
2021-02-28	4	10	5

Customer licence validity table

CustomerId	ValidFrom	ValidTo
1	2015-11-13	2017-02-10
1	2016-11-04	2018-02-10
1	2017-11-02	2019-02-10
1	2018-11-08	2020-02-10
1	2019-11-12	2021-02-10
1	2020-10-25	2022-02-10
2	2015-11-13	2017-02-10
2	2016-11-04	2018-02-10
2	2017-11-07	2019-02-10
2	2018-11-08	2020-02-10
2	2019-11-12	2021-02-10
2	2020-10-25	2022-02-10
3	2015-11-13	2017-02-10
4	2015-11-13	2021-01-01

What I need is table

Year	Month	Metrics1 sum	Metrics2 sum
2020	December	100 (customer1 license is valid in december – two valid licences in that date) + 50 (cust2 valid) + 1 (cust 4 valid)	same principle…
2021	January	110 + 55 (only customer 1 and 2 have valid license)	same principle…
2021	February	115 + 55 (only customer 1 and 2 have valid license)	same principle…

I think best result would be add new colum to metrics table with bool „validLicense“  and then use this bool in measure on sum function. But I tryed that without success…

Can you help me please?

Link to XLS file with sample data

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Try calculated column as below:

validLicensecount = 
VAR date1 = 'Metrics Table'[Sample date]
VAR Customer = 'Metrics Table'[CustomerId]
VAR tab1 =
    FILTER (
        'Customer licence validity Table',
        'Customer licence validity Table'[CustomerId] = Customer
    )
VAR count1 =
    COUNTAX (
        FILTER (
            tab1,
            date1 > 'Customer licence validity Table'[ValidFrom]
                && date1 < 'Customer licence validity Table'[ValidTo]
        ),
        'Customer licence validity Table'[CustomerId]
    ) + 0
RETURN
    count1

validLicense = 
IF ( 'Metrics Table'[validLicensecount] > 0, "Valid license" )

Filter the table visual as below:

Please check my sample file for more details.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , not able to get you logic.

But you can try a new column in table two like

new column =

var _cnt = countx(filter(Table2, Table2[customer id] =  Table1[customer id] && Table1[sample date] >= Table2[valid from]  && Table1[sample date] <= Table2[valid to]), Table2[customer id]) 

return 

_cnt*50

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8