Finding common elements

[capko]

Hello,

I have a table like the following one:

Item	Date	Value
A	01/01/2021	5
A	01/02/2021	6
A	01/03/2021	2
A	01/04/2021	1
B	01/01/2021	1
B	01/02/2021
B	01/03/2021	5
B	01/04/2021	1
C	01/01/2021
C	01/02/2021	3
C	01/03/2021	3
C	01/04/2021	1

The actual table is much longer but a simplified problem is always better to find a solution. Some items don't have a value.

I made a line chart that shows the SUM of all the values by date. The problem is that at each point I'm adding differents items since not all of them have a value (e.g. the added value for 01/2021 will be 6 -A+B- where C has no value but for 03/2021 it will be 10 -A+B+C-).

I would like to create another table for a given year (let's say 2021) where I will be able to have all the common items with a value for each month of the year:

Item	Date	Value
A	01/03/2021	2
B	01/03/2021	5
C	01/03/2021	3
A	01/04/2021	1
B	01/04/2021	1
C	01/04/2021	1

If a given item doesn't have a value for one month in the year it won't be included but all the items with values for all the months in the year will be included.

Help please ?

[vicky_]

Hey, you can try something like the below:

NewTable =
var datesWithBlank = SELECTCOLUMNS(FILTER('Table', ISBLANK('Table'[Value])), "dates", 'Table'[Date])
return FILTER('Table', NOT('Table'[Date]) in datesWithBlank)

the idea is to get a list of dates with those missing, and then use those as a filter.