Forum Discussion
Filter values by date range from table
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
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]) )
3 Replies
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")
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 + _N2Is 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.
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]) )
