Filter values by date range from table

[jimitko]

Hi all,

I'm pretty stuck with the following issue:

I do have one table with Orders (Order No., Order Date, Customer No., Value) and a second table with Customers (No., Valid since, Valid Until.). Valid since and valid Until is saying when "Value of order" should be counted into Value with date filtering.

Those tables are connected with M:1 by Customer No. 

Example of customers

Customer No.	Valid since	Valid until
1	01.01.2023	10.01.2023
2	01.01.2023	10.02.2023
3	01.02.2023	30.03.2023
4	15.01.2023	30.03.2023
5	01.01.2023	30.03.2023
6	01.01.2023	30.03.2023
1	20.01.2023	30.01.2023

Example of Orders            

Order No.	Order Date	Value	Customer No.
1	01.01.2023	55	1
2	02.01.2023	6023	2
3	03.01.2023	11651	3
4	04.01.2023	16	4
5	15.01.2023	15	1
6	16.01.2023	16	1
7	07.01.2023	6	7
8	08.01.2023	158	1
9	29.01.2023	1	2
10	30.01.2023	51	1

What I wanna do is apply the rule that should count the Value of orders only when the customer was valid and there is one more condition that one customer can be valid twice or more. In the provided example I wanna count sales for customer 1 only between dates 01.01.-10.01.2023 and 20.01.-30.01.2023. So the result for customer 1 should be counting orders 1, 8, and 10 with result Value 264 (55 + 158 + 51) and not count orders 5 and 6 as these are not in a valid period.

I tried to use the following Measure:

Value with date filtering = SUMX(FILTER(orders, orders[Order Date]>=MIN(customers[Valid since]) && orders[Order Date]<= MAX(customers[Valid until])),[Value])

 ... but without success.

Could someone please help me?

Thanks.

Jakub

[jimitko · Accepted solution]

Actually, ChatGPT provided me the correct answer code after a few attempts:

Valid Sales = 
SUMX(
    customers,
    VAR CurrentCustomer = customers[No]
    VAR ValidPeriods = 
        FILTER(
            orders,
            orders[Customer No.] = CurrentCustomer &&
            orders[Order Date] >= customers[Valid since] &&
            orders[Order Date] <= customers[Valid until]
        )
    RETURN
        SUMX(ValidPeriods, orders[Value])
)

[v-zhangti]

Hi, @jimitko 

You can try the following methods.

Measure =
VAR _N1 = CALCULATE ( SUM ( Orders[Value] ),
        FILTER ( ALL ( Orders ),
            [Order Date] >= MIN ( Customers[Valid since] )
                && [Order Date] <= MIN ( Customers[Valid until] )
                && [Customer No.] = SELECTEDVALUE ( Customers[Customer No.] )
        )
    )
VAR _N2 = CALCULATE ( SUM ( Orders[Value] ),
        FILTER ( ALL ( Orders ),
            [Order Date] >= MAX ( Customers[Valid since] )
                && [Order Date] <= MAX ( Customers[Valid until] )
                && [Customer No.] = SELECTEDVALUE ( Customers[Customer No.] )
        )
    )
RETURN
    _N1 + _N2

Is this the result you expect?

Best Regards,

Community Support Team _Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@jimitko , Create a valid column flag in you table and use that in meausre

flag =

var _cnt = Countrows(FILTER(orders, orders[Customer No]=(customers[no]) && orders[Order Date]>=(customers[Valid since]) && orders[Order Date]<= (customers[Valid until])))

return

if(isblank(_cnt),"Not Valid", "Valid")