Finding missing products

[surajdhall]

Hi Experts,

I want to find products that has missing data. I want to list the products may be in a listbox or a chart. I have below data and wanted to find the products based on below requirement.

Data:

LOAD *,
Date(MonthStart(Date),'MMM-YYYY') as MonthYear
INLINE [
Product, Date, Margin, Sales
A, 01/01/2017, 1184632, 4227592
A, 01/02/2017, 1273124, 4545040
A, 01/03/2017, 4066252, 7313400
B, 01/01/2017, 112483, 130487
B, 01/03/2017, 136175, 145455
C, 01/03/2017, 11123829, 11123879
D, 01/02/2017, 11188205, 11361659
E, 01/01/2017, 112483, 130487
];

I want to create 4 reports in the form of listbox or chart
1)showing Products list with missing data in current month, but has data in previous month.
2)showing Products list with missing data in previous month,but has data in current month.
3)showing Products list with missing data in current month and previous month, but has data in prior month.
4)showing Products list with data not missing in last 6 months.

In the above example the output would be-
report 1 should list product D only.
report 2 should list product B and C.
report 2 should list product B and C.
report 3 should list product E only.
report 4 should list product A.

Please help

[surajdhall]

Sorry, the earlier image doesn't contain last row(E)

[juraj_misina · Accepted solution]

Hi,

see attached file. Hope this helps.

Juraj

[surajdhall]

Thanks Juraj for the help.

The is exact as i expected. However from usability point of view, i want to have 4 buttons for all 4 requirements kept above the pivot table and when each button is selected, i want the pivot table to show the product list.

Can you please help!!