Forum Discussion
Filter expression by another column
Hello,
The following sales table exists:
[Entry DateTime], [Customer], [Item Sales Qty.], [Sales Amount].
Task:
For a specified period (e.g. 11/15/20 - 12/25/20, column [Entry DateTime]), take into account the amount (column [Sales Amount]) of only the first sales of customers (column [Customer]) in the total quantity of items (column [Item Sales Qty.]) not more than e.g. four (<= 4), the next sales are ignored.
Note: The required quantity of items can be sold over the entire period, but not more than the specified quantity.
Each sales line may contain a different quantity of items, also the sales amount.
---------------------------------------
An addition to the task description.
As far the function meets the conditions, it ignores the next lines.
For example, with the client ABC01524
The function, at this customer should take into account the sum of 2 items for 02.12.2020 and the sum of 2 items for 03.12.2020, the sum of the other 6 items for 03.12.2020 the function should ignore, as well as the next sale for 11.12.2020.
Here is sample of data: https://drive.google.com/file/d/1V_Qvoa_FZJZjen-0Jzai2KdopfKvG3Dw/view?usp=sharing
5 Replies
beer_not_bear - Difficult to say with specificity without testing out something. Sample data would help greatly. However, you should be able to find a MIN date using variables or EARLIER for a customer. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __PreviousThank you Greg_Deckler ,
I will try to implement your solution, if I fail I will provide a sample of data.
Have a nice evening.
Hi Greg_Deckler ,
I provided an example of the data.
I lack my knowledge to deal with it.
Please, if you can help me.beer_not_bear OK, thanks for the sample data, I have it downloaded. However, I do not fully understand what the results should be and why. Can you provide me a few rows of your expected output from the data you provided and explain how you got to those numbers?
Hello Greg_Deckler,
I tried to describe the task more in detail.
I understood that the price per unit would be needed.
That's why I added a column with the price per unit to the data sample.
But most probably this task will be hard to implement.But still.
An addition to the task description.
As far the function meets the conditions, it ignores the next lines.
For example, with the client ABC01524The function, at this customer should take into account the sum of 2 items for 02.12.2020 and the sum of 2 items for 03.12.2020, the sum of the other 6 items for 03.12.2020 the function should ignore, as well as the next sale for 11.12.2020.
