Forum Discussion

Copper Contributor

Feb 09, 2018

Pivot Table or some other way to solve this issue?

Problem: I want to create a list of all accounts that have ordered 1-3 pallets in the last 12 months of Product A but have also ordered 20 or more pallets of Product B in the last twelve months. W...

BI & Data Analytics

Need Help

SergeiBaklan

Diamond Contributor

Feb 14, 2018

Here are accounts and dates when clients ordered 1-3 of A and less than 20 of B. If the task is to select accounts who ordered 1-3 of A and <20 of B totally for entire year, when that could be a Pivot Table if added it to data model when creating, and a measure like

=IF(CALCULATE(AND(SUM([Product A])<=100,SUM([Product B])<=200),Tableau1[Dates] >= TODAY()-365),1,0)

(no initial combination in the sample table, thus i took another figures), after that filter value in pivot table where that measure =1. As attached.

Mehdi HAMMADI

Brass Contributor

Feb 14, 2018

Hello,
When reading your answer I just realized that I had misunderstood Dan's question.
I understood that the customer had at least placed an order with 1-3 pallets of product A and 20 pallets or more of product B during the last 12 months. which means that my answer is out of context.
While it is a question of the sum of the orders of product A and product B for the last 12 months

But I'm not sure that the formula you posted can solve the issue