Forum Discussion
Cumulative Sum - 2 conditions
Hey everyone !
I have done many tests here but can't find any solution, any help is appreciated.
I have a complex dashboard but I simplified it here in order to focus on the issue :
I have three tables : Validity_List, Possibility_List and Sales_Start. Here are some extracts of theses tables :
Validity_List : All sales of product/color per period of sales (ID is the combination of Color/Product/Sales start/Sales end)
| Color | Product | Sales start | Sales End | Quantity | ID |
| A | P1 | 0 | 0 | 86 | A_P1_0_0 |
| A | P1 | 1 | 0 | 3 | A_P1_1_0 |
| A | P1 | 1 | 1 | 1 | A_P1_1_1 |
| B | P32 | 0 | 0 | 2 | B_P32_0_0 |
| C | P32 | 0 | 0 | 5 | C_P32_0_0 |
| D | P2 | 0 | 0 | 2 | D_P2_0_0 |
| D | P2 | 12 | 11 | 1 | D_P2_12_11 |
| D | P2 | 12 | 10 | 1 | D_P2_12_10 |
| D | P2 | 41 | 34 | 2 | D_P2_41_34 |
| D | P32 | 0 | 0 | 63 | D_P32_0_0 |
Possibility_List: All combination of color/product/sales start and sales end possible. It also includes for other purposes combination that do not exist in validity_list. (ID is the combination of Color/Product/Sales start/Sales end)
| Color | Product | Sales Start | Sales End | ID |
| A | P1 | 0 | -1 | A_P1_0_-1 |
| A | P1 | 0 | 0 | A_P1_0_0 |
| A | P1 | 1 | -1 | A_P1_1_-1 |
| A | P1 | 1 | 0 | A_P1_1_0 |
| A | P1 | 1 | 1 | A_P1_1_1 |
| A | P1 | 2 | -1 | A_P1_2_-1 |
| A | P1 | 3 | -1 | A_P1_3_-1 |
| B | P1 | 0 | -1 | B_P1_0_-1 |
| B | P32 | 0 | -1 | B_P32_0_-1 |
| B | P32 | 0 | 0 | B_P32_0_0 |
| C | P32 | 0 | 0 | C_P32_0_0 |
| D | P2 | 0 | 0 | D_P2_0_0 |
| D | P2 | 12 | 11 | D_P2_12_11 |
| D | P2 | 12 | 10 | D_P2_12_10 |
| D | P2 | 41 | 34 | D_P2_41_34 |
| D | P32 | 0 | 0 | D_P32_0_0 |
Sales_Start: All possible sales starts (
| Sales Start |
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 20 |
| 27 |
| 34 |
| 41 |
The tables are organised this way :
What I need is a measure that for every value of [Sales start]Sales start would dislay the quantity sold from the validity tables where at the same time:
[Sales start]Sales start <= [Validity_List]Sales start
[Sales start]Sales start > [Validity_List]Sales End
Thank you !
3 Replies
Hi datadax123 ,
I would appreciate it if you could give me the expected results, thank you.
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey Anonymous ,
In this example it would be :
Sales Start Quantity 0 1 3 2 3 4 5 6 7 8 9 10 11 1 12 2 13 20 27 34 41 2 Hi datadax123 ,
Below is my table1:
Below is my table2:
Below is my table3:
The following DAX might work for you:
Measure = var _sale = SELECTEDVALUE(Sales_Start[Sales Start]) var _val_start = SELECTEDVALUE(Validity_List[Sales start]) var _val_End = SELECTEDVALUE(Validity_List[Sales End]) var _Quan = SELECTEDVALUE(Validity_List[Quantity]) RETURN IF(_sale <= _val_start && _sale > _val_End , _Quan , BLANK())The final output is shown in the following figure:
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
